Inconsistent results from COUNT(DISTINCT(COLLECT

firestorm

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?

Mark Cronk

Hi @firestorm ,

I suspect your issue is caused by the time stamp in your submission date column. The dates and times look right by when used in a formula produce UTC results not local time. To check it, insert a column [date only] and insert the column formula =DATEONLY([Submission Date]@row). You'll likely find that some of the dates returned don't match your submission column.

There are some techniques to correct that if you find it's the issue.

Mark

Mark Cronk

Hi @firestorm ,

I suspect your issue is caused by the time stamp in your submission date column. The dates and times look right by when used in a formula produce UTC results not local time. To check it, insert a column [date only] and insert the column formula =DATEONLY([Submission Date]@row). You'll likely find that some of the dates returned don't match your submission column.

There are some techniques to correct that if you find it's the issue.

Mark

firestorm

Hello @Mark Capretta

You are right. Can you let me know how to resolve this? I tried the formula in https://community.smartsheet.com/discussion/68947/formulas-for-calculating-time but results still are inaccurate.

I copied @Paul Newcome's formula and though the result is showing 8 hours after UTC, this is not the date that I need. The date/time in the first column is correct but the formulas I used to calculate DISTINCT values doesn't take the earlier time into account because if the person checks in before 8am, it's considered as the day before since it's UTC.

How do I force the formula to take in the right time, rather right date?

firestorm

Hello @Mark Cronk,

Sorry I tagged the wrong name in my reply.

Any advice?

Kelly Moore

Hey @firestorm

A workaround to a timestamp problem is to use the new automation function Record Date. You'll need a helper Date column for this date. Set the automation to trigger off of change/add rows and look at the Created date column for the change. The date that is recorded is your local date and can be used in calculations. Note this is date only- not date-time. I parse the time out into another field from the Created field if I need time- and the time portion is already in local time.

cheers

firestorm

Thanks @KDM, this works perfectly. Can you share how you parse the time into your local time? I am sure you are not just using =RIGHT formula?

My 2nd issue is also not solved, waiting for Smartsheet Support team's help.

Kelly Moore

Hey @firestorm

I do use the =RIGHT unless I'm doing further calculations to convert to military time. The length changes with single digit and double digit hours so an IF statement is needed. It was surprising to me to discover the time piece of the date stamp remained in local time, which is lucky because that would be more difficult to maintain as a constant as many regions shift between daylight savings on and off - and UTC-0 does not.

If you need to do time calculations, see Paul's collection of calculations here

Here's one formula I use just to extract time from the timestamp.

=IF(LEN(Created@row) = 17, RIGHT(Created@row, 8), RIGHT(Created@row, 7))

cheers

Kelly

Kelly Moore

@firestorm

I forgot about your 2nd issue. I wondered if you were having a problem with the TODAY() function 'losing' what day it was so not updating consistently. The TODAY() function in smartsheet, just like in excel, needs activity to somehow remain synched to the date. Automation running, saving the sheet, opening the sheet, update requests being returned etc are sheet activities that keep it in sync. If the sheet lies dormant with no activity, it might lose what day it is and perhaps not update properly? If you see the inconsistencies on Monday's, this might point toward that problem.

firestorm

https://community.smartsheet.com/discussion/comment/277129#Comment_277129

Hi @KDM

Yes, you are right. Hence, I have tried to use the Lock / Unlock row automation in the source sheet, hoping that the sheet will save by itself once the the workflow is triggered. However, it still doesn't "refresh" the results to show the right numbers in my dashboard.

Andrée Starå

Hi @firestorm

Please have a look at my post below with a method I developed to update the sheet(s) daily.

More info: 

https://community.smartsheet.com/discussion/74527/daily-update-solution-without-the-need-to-open-the-sheet-use-zapier-or-similar

Would that work/help?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please help the Community by marking it as the accepted answer/helpful. It will make it easier for others to find a solution or help to answer!

firestorm

Hello @Andrée Starå

The record date automation currently works well in the helper column.

The issue I have now is the calculations using this COUNT(DISTINCT(COLLECT formula that doesn't update the results, if the sheet is closed, as I am using TODAY() as part of the formula, as mentioned correctly by @KDM.

Is there a workaround to force the source sheet to refresh, so that the results are correct?

Andrée Starå

@firestorm

That is what you'd use my method for.

Kelly Moore

@firestorm

Another alternative, if you have access to it, is Smartsheet's premier app Bridge.

cheers

firestorm

https://community.smartsheet.com/discussion/comment/277167#Comment_277167

Hello @Andrée Starå

May I ask how I could use your method for instant update as and when a new submission is added to the source sheet? This is for daily monitoring of people coming to our buildings so we need "instant" update on the number of people in the buildings to be shown in the dashboard.

firestorm

https://community.smartsheet.com/discussion/comment/277202#Comment_277202

@KDM

Unfortunately, no.