Date in variable

[#1]

Hi,

I like to set up the KPI chart in my sense dashboard in the cloud so that it always show the figure of yesterday. When I hardcode the date it works but not in any other way

I'm a newbie and appreciate any help...

----this is the one working----

sum({$ <[EVENT_DATE]={"16-MAY-16 12.00.00"}>} TOTAL_DISCREPANCY_VALUE_OVER)

So here is all the different methods I tried and failed.

----without variable----

sum({$ <[EVENT_DATE]={"=$(=date(today()-13,'DD-MMM-YY hh.mm.ss'))"}>} TOTAL_DISCREPANCY_VALUE_OVER)

----without variable----

LET v_date = date(today()-14,'DD/MM/YYYY');

sum({1 <[Date(Date#(EVENT_DATE,'DD-MMM-YYYY hh.mm.ss'),'DD/MM/YYYY')]={$(=v_date)}>} TOTAL_DISCREPANCY_VALUE_OVER)

----without variable 2----

LET v_date2 =date(today()-14,'DD-MMM-YY hh.mm.ss')

sum({$ <[EVENT_DATE]={"$(=v_date2)"}>} TOTAL_DISCREPANCY_VALUE_OVER)

sum({$ <[EVENT_DATE]={$(=v_date2)}>} TOTAL_DISCREPANCY_VALUE_OVER)

[swuehl]

Try either

sum({$ <[EVENT_DATE]={'$(=date(today()-13,'DD-MMM-YY hh.mm.ss'))'}>} TOTAL_DISCREPANCY_VALUE_OVER)

or

sum({$ <[EVENT_DATE]={'$(v_date2)'}>} TOTAL_DISCREPANCY_VALUE_OVER)

Note that you are using -13 in the first expression vs -14 in your variable.

[#3]

Tried both, first one comes back with null and second one returns 0

[swuehl]

Which exact value are you trying to match?

16-MAY-16 12.00.00  is noon May 16, while today() will return today at midnight (and if you subtract some days, it's still midnight).

Don't you have a real date field?

If not, you can try

sum({$ <[EVENT_DATE]={"=[EVENT DATE]>=$(=Floor(today()-13)) AND [EVENT DATE] <$(=Floor(today()-12))"}>} TOTAL_DISCREPANCY_VALUE_OVER)

[#5]

event_date is the date filed in the 'DD-MMM-YY 12.00.00' format. The sample data that I loaded for testing is from 01-MAY-16 till 23-MAY-16

Looks like 12.00.00 is causing the problem?

I tried this one too, it returns 0

[#6]

I tried your first suggestion with an slight alteration (12.00.00 instead of hh.mm.ss) and it works

sum({$ <[EVENT_DATE]={'$(=date(today()-14,'DD-MMM-YY 12.00.00'))'}>} TOTAL_DISCREPANCY_VALUE_OVER)

but the variable one still doesnt work even-though I changed it to

date(today()-14,'DD-MMM-YY 12.00.00')

[swuehl]

Does EVENT_DATE show a numeric representation?

Why don’t my dates work?

Get the Dates Right

Dates in Set Analysis

If possible, upload a small sample QVF.

[#8]

Thanks for the links, I have a better understanding now

No it doesn't. It is totally a messed up format but I gotta fix it now.

Thanks alot!

Sara