topic Re: Working with Variables in QlikView

Working with Variables

Sat, 29 Jun 2013 11:15:33 GMT

Hello QlikCommunity,

I have this small problem , i've set up 2 variables to present the from and to date selected by a user to determine between which dates he would like to see his sales.

now , if i want to compare those to let's say 7 days before the given date , what can i do ? i thought it was simple and i used this formula :

(=if(PeriodDate>=FromDate-7 AND PeriodDate <=ToDate-7 , sum(sales))

but it did not work . when i remove the "-7" from both ends , it works and shows me ,well, the sales between the period dates , which are btw numbers translated into dates par example : 41445 , etc. . .

why cant QlikView accept substracting 7 from a variable that is determined as, par example, 41446 ?

Re: Working with Variables

hic — Sat, 29 Jun 2013 12:35:10 GMT

There can be several things, but the most likely is that your FromDate is a string (like '2013-01-01') that gets automatically interpreted when it is the operand in the comparison. But if the operand is '2013-01-01'-7 then you will have a type mismatch in the subtraction that results in the operand being NULL.

Try using numeric variables, or use an interpretation function in your expression, e.g.

PeriodDate>=Date#(FromDate)-7

A second, unrelated problem might occur from the fact that PeriodDate is unaggregated. So if you have several possible values, this will be undetermined. Use the following instead:

Max(PeriodDate)>=Date#(FromDate)-7

HIC

Re: Working with Variables

Mon, 01 Jul 2013 12:23:22 GMT

actually Henric , my FromDate and ToDate variables are numbers , as in the user picks them from a calendar and are presented in a number such as 41233 , not 2013-01-01 . i tried the first formula , and it gave me the same outcome as my old one , and the second just gave me an error.

Re: Working with Variables

Mon, 01 Jul 2013 12:33:32 GMT

Try below process

LET vFmDate = peek('ORIGINALDAY',0,'Maxtimes');

LET vFromDate = Date(MonthStart(vFmDate,1),'YYYYMMDD');
LET vToDate = Date(MonthEnd(vFmDate,1),'YYYYMMDD');

Regards-bika

Re: Working with Variables

Mon, 01 Jul 2013 13:08:30 GMT

Hi,

Try the below expression:

(=if(PeriodDate>=date(date(FromDate)-7) AND PeriodDate <=date(date(ToDate)-7) , sum(sales))

Regards,

Venu.

Re: Working with Variables

Mon, 01 Jul 2013 13:41:29 GMT

Perfect , you're a genius .