Date comparison in set analysis

[#1]

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,

[swuehl]

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]

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

[#4]

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,