Qlik Community

Ask a Question

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Announcements
Welcome to our newly redesigned Qlik Community! Read our blog to learn about all the new updates: READ BLOG and REPORTED ISSUES
cancel
Showing results for 
Search instead for 
Did you mean: 
Contributor II
Contributor II

Date Variable in Expression

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;

Set1.PNG

 

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;

set2.PNGset3.PNGset4.PNG

 

 

Any suggestions?

 

Thank you!

 

Labels (2)
1 Solution

Accepted Solutions
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

2 Replies
MVP & Luminary
MVP & Luminary

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

Regards,
Kaushik
If reply is satisfactory, please "Like" the post.
If reply is solution, please mark as "Correct".

View solution in original post

Contributor II
Contributor II

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))