Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
mlundborg
Contributor
Contributor

Sum field based on a measure

Hi,

I have the following data: Revenue, Profit, Customer and YearMonth.

I need to show the combined Revenue and count of Customers in a given month (Dimension in my chart) that has a margin (Profit / Revenue) below a given threshold. The threshold is controlled by a slider, who's value is stored in a variant (vSlider).

I have tried different variations of formulas with aggr, rangesum, if etc. without luck.

One example:

sum(if(aggr(sum([Profit])/sum([Revenue]), Customer, YearMonth)<=vSlider,[Revenue]))

Can someone help me crack this?

Thanks in advance

1 Solution

Accepted Solutions
rubenmarin

Hi Martin, in that expression the check with vslider should be inside the aggr:

sum(aggr(if(sum([Profit])/sum([Revenue])<=vSlider,[Revenue]), Customer, YearMonth))

If doesn't works a sample would be a good idea to make some tests.

View solution in original post

3 Replies
YoussefBelloum
Champion
Champion

Hi,

try to attach sample app or sample file

rubenmarin

Hi Martin, in that expression the check with vslider should be inside the aggr:

sum(aggr(if(sum([Profit])/sum([Revenue])<=vSlider,[Revenue]), Customer, YearMonth))

If doesn't works a sample would be a good idea to make some tests.

mlundborg
Contributor
Contributor
Author

Thanks rubenmarin

It worked, I only had to add a sum to the [Revenue] in the if statement. So it ended up looking like this:

sum(aggr(if(sum([Profit])/sum([Revenue])<=vSlider,sum([Revenue])), Customer, YearMonth))