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