5 Replies Latest reply: Apr 16, 2009 7:02 PM by Mark McCoid

# can't get sumif formula to work correctly

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

• ###### can't get sumif formula to work correctly

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)`

• ###### can't get sumif formula to work correctly

My code formatting is a bit off but hopefully you get the picture.

• ###### can't get sumif formula to work correctly

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.

• ###### can't get sumif formula to work correctly

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

• ###### can't get sumif formula to work correctly

Here is an example using QlikView's Set notation:

`SUM({\$<MOTRenewal={">\$(=Today())<=\$(=(Date(Today()+14)))"}>} FieldToAggregate))`

Hope this helps.

Mark