Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi i have the expression below;
Sum
(IF(MOTRenewal=today()+14,1))
This works however it doesn't do what i need it to. I want it to show all MOT's due from today up until 14 days going forward. The expression above however only shows whats due in 14 days, so for example it will only show the MOT's due on the 30th April as that is 14 days from today and nothing else, I need it to show everything else in between today and the 14 days. How can I get this to work please assist.
Sunny
Hi,
Something like this ought to work:
Sum
(IF(MOTRenewal>=Today() AND MOTRenewal<=Today()+14, Then, Else))
Or using Set Analysis to do a range search:
sum
({$<MOTRenewal={>=$(=Today())<=$(=date(Today()+14))}>} FieldName)
Hi,
Something like this ought to work:
Sum
(IF(MOTRenewal>=Today() AND MOTRenewal<=Today()+14, Then, Else))
Or using Set Analysis to do a range search:
sum
({$<MOTRenewal={>=$(=Today())<=$(=date(Today()+14))}>} FieldName)
My code formatting is a bit off but hopefully you get the picture.
Set analysis is the better way to do this (the second example JSN suggests) as it will allow the expression to calculate correctly, even if the user selects something in [MOTRenewal]. Also, keep in mind that if you have other date fields connected to [MOTRenewal] and the user selects in that, the calculation will change.
Hi, thanks for that the code, i just had to change it slightly and it worked, i used;
Sum
(IF(MOTRenewal>Today()AND MOTRenewal<=Today()+14,1))
One again thanks for your help!
Sunny
Here is an example using QlikView's Set notation:
SUM({$<MOTRenewal={">$(=Today())<=$(=(Date(Today()+14)))"}>} FieldToAggregate))
Hope this helps.
Mark