Date Variable in Expression

[Quamie]

Hi All,

I'm having difficulty getting a date variable to work as part of my set analysis.

I'm using a Hierarchy Table, and HierarchyBelongsTo table to produce a listbox with totals;

My expression works with a hardcoded date, but I can not seem to get it to function with a variable.

SUM({1}if(ParentID=AncestorID,aggr(Count({1<_DateField={'2/28/2019'}>}DISTINCT([Person Number])),AncestorID),0))

I've tried a few different solutions found on these forums, but can't seem to get anything to work.

The closest I've gotten is;

SUM({1}if(ParentID=AncestorID,aggr(Count({1<_DateField={"=$(=Date(vDateField, 'M-D-YYYY'))"}>}DISTINCT([Person Number])),AncestorID),0))

This returns a value, but not the correct values.

Using a Calendar Object to set the date Variable;

Any suggestions?

Thank you!

[kaushiknsolanki · Accepted solution]

Hi,

This should work, just changed the Date format.

SUM({1}if(ParentID=AncestorID,aggr(Count({1<_DateField={"=$(=Date(vDateField, 'M/D/YYYY'))"}>}DISTINCT([Person Number])),AncestorID),0))

If it doesn't, then create a new straight table, add this as expression (No Dimension & No Label to the Expression). After Apply, just take your cursor on the label of the expression and check if the $() in Set analysis gave you right date.

Regards,

Kaushik Solanki

[Quamie]

Now I feel dumb!
Thank you for your help 🙂
One minor adjustment, had to get rid of an = character in the string

SUM({1}if(ParentID=AncestorID,aggr(Count({1<_DateField={"$(=Date(vDateField, 'M/D/YYYY'))"}>}DISTINCT([Person Number])),AncestorID),0))