Hello,
I am using a separate sheet to calculate unique names (employees/visitors) who have visited the respective locations, separated by morning and afternoon sessions. I was using Sheet Summary in the source sheet but the numbers don't refresh by itself for some reasons, hence I have created a "metrics" sheet. The results are shown as Metrics in a dashboard.
For employees, this is the formula:
=COUNT(DISTINCT(COLLECT({Form Name}, {Form Work Location}, "Building A", {Form Date}, TODAY(), {Form Session}, "Morning")))
1st Problem: Somehow, the result for Building A is not accurate though it's ok for Building B. I have no idea why.
For non-employees, this is the formula:
=COUNT(DISTINCT(COLLECT({Form Visitor Name}, {Form Visitor Location}, "Building A", {Form Date}, TODAY())))
2nd Problem: The results are accurate for non-employees but the numbers seem to change by itself occasionally, not sure if it's because there are new records added in the source sheet. I have to go into the metrics sheet to refresh (pressing F5) and save the file before it will show accurately in the dashboard.
Any advice?