Skip to main content
Announcements
Qlik Community Office Hours, March 20th. Former Talend Community users, ask your questions live. SIGN UP
cancel
Showing results for 
Search instead for 
Did you mean: 
hh2019
Contributor II
Contributor II

Limit Monthly Sum

Hello guys,

I would like to limit the monthly sum of a field to 30.

I have tried with the following expression:

if(Sum(FIELD)>30,30,Sum(FIELD))

But it has the problem of also limiting my overall sum (ie: yearly) to 30, which isn't ideal.

The desired result is to sum the field for each month, and if some of the months are over 30, use 30 as the sum of the field for that specific month.

I hope my question is clear, but in case it isn't please feel free to ask me for more information.

Thanks for the help 🙂

HH

Labels (2)
1 Solution

Accepted Solutions
sunny_talwar

May be this if Month is the only dimension where you are using the below expression

Sum(Aggr(If(Sum(FIELD) > 30, 30, Sum(FIELD)), Month))

 

View solution in original post

2 Replies
sunny_talwar

May be this if Month is the only dimension where you are using the below expression

Sum(Aggr(If(Sum(FIELD) > 30, 30, Sum(FIELD)), Month))

 

hh2019
Contributor II
Contributor II
Author

Thanks Sunny_talwar, it worked!

I only had to change it to:

Sum(Aggr(If(Sum(FIELD) > 30, 30, Sum(FIELD)), Month, MyOtherDimension))

🙂

HH