topic Yesterday variable in QlikView

Yesterday variable

Mon, 02 Sep 2013 09:51:48 GMT

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

Re: Yesterday variable

swuehl — Mon, 02 Sep 2013 09:56:46 GMT

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)

Re: Yesterday variable

Mon, 02 Sep 2013 09:59:21 GMT

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

Re: Yesterday variable

swuehl — Mon, 02 Sep 2013 10:06:06 GMT

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?).

Re: Yesterday variable

Mon, 02 Sep 2013 10:11:17 GMT

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?

Re: Yesterday variable

swuehl — Mon, 02 Sep 2013 10:18:07 GMT

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

Re: Yesterday variable

Mon, 02 Sep 2013 10:46:52 GMT

It worked to convert Date_clean to numeric dates.