Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Date in variable

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)

7 Replies
swuehl
MVP
MVP

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.


Not applicable
Author

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

swuehl
MVP
MVP

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)

Not applicable
Author

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

Not applicable
Author

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
MVP
MVP

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.

Not applicable
Author

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