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 ?
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:
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.
.png "Former Employee"){kind=icon}