topic Re: Date comparison in set analysis in QlikView

Date comparison in set analysis

Tue, 04 Nov 2014 16:33:29 GMT

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,

Re: Date comparison in set analysis

swuehl — Tue, 04 Nov 2014 17:02:39 GMT

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.

Re: Date comparison in set analysis

pedro_tavares — Tue, 04 Nov 2014 23:33:42 GMT

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

Re: Date comparison in set analysis

Wed, 05 Nov 2014 06:13:41 GMT

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,