Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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
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