topic Re: set analysis / aggr function in QlikView

set analysis / aggr function

Zwax — Sat, 16 Nov 2024 01:05:48 GMT

Hi gurus

I am having some set analysis / aggr function issues. I am trying to sum some specific values.

Conditions:

If Area = 34 and total amount within S_ID and Item is under 10
Then sum amount only for Type AB

There are multiple items and other areas. For the simplicity in the sample, I kept only one.

I want to show Date, Item and the calculation.

As you can see in the sample the total sum of all Type AB’s is 45. However, the 2 lines marked with yellow should not be counted in the calc, since the total amount is above 10 (total and not type specific). The result should be 29 (45-12-4 = 29).

Test 3 gives the right result if I add the dimension of S_ID, but not without it.

Something in Test 2 do not calculate correct, if both types is present or in case of duplicates (the orange ones).

Expression:

Test 1: sum(if(aggr(Sum({<Area={34}>}Amount),S_ID,Item)<10,sum(if(Type='AB',Amount))))

Test 2: sum(if(aggr(Sum({<Area={34}>}Amount),S_ID,Item)<10 and Type='AB',Amount))

Test 3: if(aggr(Sum({<Area={34}>}Amount),S_ID,Item)<10,sum(if(Type='AB',Amount)))

Any ideas of how to accomplice this?

Re: set analysis / aggr function

sunny_talwar — Tue, 10 Mar 2020 14:22:02 GMT

Try this

Sum(Aggr(If(Sum({<Area = {34}>} Amount) < 10, Sum({<Type = {'AB'}>} Amount)), Date, S_ID, Item))

Re: set analysis / aggr function

Zwax — Wed, 11 Mar 2020 07:02:22 GMT

@sunny_talwar wrote:
Try this

Sum(Aggr(If(Sum({<Area = {34}>} Amount) < 10, Sum({<Type = {'AB'}>} Amount)), Date, S_ID, Item))

Nice. This solved the issue. Had to place the Area = {34} in the next sum as well to get the result I needed.

Sum(Aggr(If(Sum({<Area = {34}>} Amount) < 10, Sum({<Area = {34}, Type = {'AB'}>} Amount)), Date, S_ID, Item))

Thank you so much 😀👍