Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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.
Tried both, first one comes back with null and second one returns 0
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)
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
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')
Does EVENT_DATE show a numeric representation?
If possible, upload a small sample QVF.
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