topic Re: How to sum values only when they are above a certain level in QlikView

How to sum values only when they are above a certain level

cleblois — Wed, 03 Feb 2016 17:10:27 GMT

Hi,

I'm trying to make calculation on Part Numbers only if their total quantities are above a certain value.

Threshold value is in a variable (vLower)

Measure below is sending figures that are not what I expect. I suspect it to sum only unit Quantity above the threshold.

=Sum(if(PN=aggr(if(sum(Quantity)>=$(vLower), PN), PN), Quantity))

Notes

* for testing I created below dimension to flag the PN I had an interet in. This works

=aggr(if(sum(Quantity)>=$(vLower), DPN), DPN)

* I also tried to build a set but with no success

=Sum({<PN=P({<PN={"aggr(if(sum(Quantity)>=$(vLower), PN)"}>} PN)>} Quantity)

Any help or hint would be more than welcomed

Thx

Re: How to sum values only when they are above a certain level

Oleg_Troyansky — Wed, 03 Feb 2016 18:25:41 GMT

Hi Christophe,

I think you are overly complicating your solution. All you need is a static dimension PN (no need to create a dynamically calculated dimension, just let QlikView filter the dimension values based on the Expression).

In the expression, you just need a Set Analysis with an Advanced Search filter like this (just edited it, it wasn't simplified enough...):

=Sum({<PN={"=sum(Quantity)>=$(vLower)"}>} Quantity)

- You don't need AGGR, because a filter on the field PN serves as an implied AGGR by PN

- Your don't need an IF statement, because the Advanced Search, enclosed in double quotes and starting with the equal sign {"=..."}, serves as a condition.

This is it. It's actually a lot simpler than you thought.

Cheers,

Oleg Troyansky

Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

Re: How to sum values only when they are above a certain level

cleblois — Wed, 03 Feb 2016 18:33:39 GMT

You're great! I spent the afternoon on this ...

Just one copy paste correction: =Sum({<PN={<PN={"=sum(Quantity)>=$(vLower)"}>} Quantity)

Thx,

Re: How to sum values only when they are above a certain level

MK_QSL — Wed, 03 Feb 2016 18:47:43 GMT

=SUM({<PN = {"=SUM(Quantity) >= $(vLower)"}>}Quantity)

Re: How to sum values only when they are above a certain level

Oleg_Troyansky — Wed, 03 Feb 2016 18:50:52 GMT

Yeah, I copied your formula and I didn't remove enough 🙂 thanks to Manish for correcting it!