Dashboard Discrepancy?

​@chrislower In SQL, all date/time fields are stored in UTC time, and the UI adjusts this based on the user time zone. Is it possible that one of the dates on the appointment is late enough in the day that it actually is stored in the following day in SQL? I’ve seen this type of issue many times in the past, so it seems like it could be similar

I thought about that, but the Hours Opened calculation in the GI actually adjust for UTC, I believe.  What is odd is the KPI actually shows the right amount, it just doesn’t drill down to the right number of Appointments.

​@BenjaminCrisman 

You may be correct, maybe the KPI tile is being adjusted for the UI but the GI is not.  I went and looked at the GI and the created date is being manually adjusted for UTC but it doesn’t look like the hour calculation is.

=DateDiff('h', [FSAppointment.CreatedDateTime], Now() )

What would be the best way to manually adjust for EST (-5) in the calculation or is there a better way to handle.

Thanks,

Chris

​@chrislower You can use the DateAdd function and correct -5 from the [FSAppointment.CreatedDateTime]. Or you could force UTC on the field and then the dashboard widget should display what is being seen in the GI. Anytime a date field (or any field) is used in an expression it will force the database value over the UI value. So something like an ID field which is an INT in the database may show as a name in the UI, but if you used that field in an expression it would instead return the INT value.

​@BenjaminCrisman 

Not the greatest at syntax yet, I tried to nest both of those expressions to get a hr adjusted result but it didn’t seem to work the way I wanted.

=dateadd(DateDiff('h', [FSAppointment.CreatedDateTime], Now() ),'h', -5)

Is there a better way to do that.

Thanks for all your help!

So I have troubleshot a little further, I am not sure UTC is the issue (the offending record is older than the one that is showing).  I believe it is a function of how the GI is treating the filters vs how the KPI is treating the same filter.

In the KPI things seem to work as normal but when the same filter goes into GI the result changes.  As long as the missing result’s Scheduled Start Date is today it is excluding it from the filter.  It is only supposed to do that if the Validated By Dispatcher is checked.  

Any thoughts as to why the GI filter would function differently or am I missing something.

In this example once I changed the Scheduled Start Date to 2/24 it worked as expected in both the KPI and the GI.  But if the Scheduled Start Date matches today with the check box or not, it will show in the KPI but not in the GI.

​@BenjaminCrisman 

Typically always something simple, you are correct.  The Dashboard was ignoring the extra bracket but when the filter was being transferred over to the GI it attempted to correct the extra bracket and added an ending bracket.  From there it was making the last statement an “And” statement.  Odd how it translated that over, but it definitely did the trick.  Appreciate the help!!