Skip to main content
Announcements
Do More with Qlik - Qlik Cloud Analytics Recap and Getting Started, June 19: REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date comparison in set analysis

Hi,

I have a set analysis which is have date range. All objects Validdate, StartDt and EndDt has date and timestamp. The below expression is working but left side has a date and timestamp right side has date only. So I tried to use Date(Validdate) but it is not working.

Sum({< Validdate = {">=$(=Date(StartDt,'M/D/YYYY')<=$(=Date(EndDt,'M/D/YYYY')"} >} Amt)  -- Working

Sum({< Date(Validdate,'M/D/YYYY') = {">=$(=Date(StartDt,'M/D/YYYY')<=$(=Date(EndDt,'M/D/YYYY')"} >} Amt)  -- not working

I can create a variable like Date(Validdate,'M/D/YYYY') and can use in the expression, I am not sure why it is not working if I use Date(Validdate,'M/D/YYYY') in the expression.

Can you help me in this regard.

Thanks,

3 Replies
swuehl
MVP
MVP

Because you can only use field names on the left side of your set expression modifier not expressions (it's modifying selection in a field). See also the Help for valid syntax.

So your idea is right, create a field in your data model from Validdate containing only date and use this in your set modifier.

pedro_tavares
Contributor II
Contributor II

On creating a new field on your script, use floor, or else it will just format the field, keeping the "original" format internally.

Use: date(floor(Validdate)) as nameField

This way it will remove the time part.

regards

Not applicable
Author

I created a variable, like Var1 = Date(floor(Validdate))  and used in the set analysis but id didn't worked...

Sum({< Var1 = {">=$(=Date(StartDt,'M/D/YYYY')<=$(=Date(EndDt,'M/D/YYYY')"} >} Amt)

Thanks,