Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

1 Solution

Accepted Solutions
Anonymous
Not applicable
Author

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)


View solution in original post

5 Replies
Anonymous
Not applicable
Author

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)


Anonymous
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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



markmccoid
Partner - Creator II
Partner - Creator II

Here is an example using QlikView's Set notation:


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


Hope this helps.

Mark