Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
hh2019
New 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 (3)
1 Solution

Accepted Solutions

Re: Limit Monthly Sum

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

 

2 Replies

Re: Limit Monthly Sum

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
New Contributor II

Re: Limit Monthly Sum

Thanks Sunny_talwar, it worked!

I only had to change it to:

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

🙂

HH