Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Yesterday variable

Hello,

I have this in my data:

A column with dates like:

Date_Clean: 2013-09-01

I have these variables in my edit script:

LET vDateToday = num(Date(Today(), 'YYYY-MM-DD'));

LET vYesterday = num(date(vDateToday-1, 'YYYY-MM-DD'));

LET vPast15Days = num(date(vDateToday-15, 'YYYY-MM-DD'));

And this variable in my document:

vLast15Days           ='>=$(vPast15Days) <=$(vDateToday)

I want to show sum(amount) on dates - my problem is:

This one works fine:

=sum({<Date_Clean={'$(vLast15Days)'}>} TotalPrice)

but this one doesnt not work:

=sum({<Date_Clean={'$(vYesterday)'}>} TotalPrice)

my output for the 3 variables are:

vDateToday= 41519

vYesterday = 41518

vLast15Days = >=41504 <= 41519

So why does my Yesterday variable not show any sum ? When it is included in vLast15Days..

1 Solution

Accepted Solutions
swuehl
MVP
MVP

Right, this should eliminate the need to care for a special format.

View solution in original post

6 Replies
swuehl
MVP
MVP

Probably because the field selection modifier expects a different date format.

If you Date_Clean is formatted like 'YYYY-MM-DD', try

=sum({<Date_Clean={'$(=Date(vYesterday,'YYYY-MM-DD))'}>} TotalPrice)

Not applicable
Author

Thats not working And why should it suddenly except a differnt format? when i made my variable with that format?

LET vYesterday = num(date(vDateToday-1, 'YYYY-MM-DD'));


And vLast15Days is working fine? based on the same

swuehl
MVP
MVP

Because QV may evaluate a search expression like ">StartDate<EndDate" differently compared to a plain value.

Try inputting the value without a variable or dollar sign expansion. Try also to select the date value in a list box only using the plain sum(TotalPrice).

I assume you have TotalPrice values for that date (maybe not - it was a sunday?).

Not applicable
Author

I have amount on that date (so if i select the date with just sum(totalprice) i can see the amount.

Ill try the other thing,

and then i will try to load date_Clean as num(Date_clean) <- That should be best practice right?

swuehl
MVP
MVP

Right, this should eliminate the need to care for a special format.

Not applicable
Author

It worked to convert Date_clean to numeric dates.