Dates in set analysis

[#1]

I need to include a date parameter and an additional calculation within my existing set anlaysis expression. I had originally try to include it in a varilable but that was causing problems. My current expression using the variable is below:

if(aggr(count({$ <ObserveDate = {">=$(varDateParameter)"}, ChoiceCTCustomerDescription = >} ChoiceCTReferenceId) - count({$ <ObserveDate = {">=$(varDateParameter)"}>} ChoiceCTReferenceId) <= 0, StaffRecnum), StaffName, null())

varDateParameter code is below:

num(varLookBackDate +(only (FacilityTimezoneOffset) / 24) - (varHours / 24))

I need to figure out how to get the varDateParameter part of code into the set analysis expression without using a variable. Any help would be greatly appreciated. Thank you!

[Oleg_Troyansky]

the variable is not the problem... Date use in Set Analysis is very tricky. In order to compare dates in set analysis, you have to format the value EXACTLY the same way your Date Field it formatted - I mean, even the difference between 'M/D/YYYY' and 'MM/DD/YYYY' can break it.

If your Date Field "ObserverDate" is formatted as 'MM/DD/YYYY' (look it up in Document Properties, Number) - then format your variable value the same way:

date(varLookBackDate +(only (FacilityTimezoneOffset) / 24) - (varHours / 24), 'MM/DD/YYYY')

Then it should work, provided that everything else is in order.

good luck!