Multiple Aggr() in Pick/Match function

[#1]

Hello all,

I have the following function (field names changed):

pick(
match(valuelist('Red','Blue'),'Red','Blue'),
(
sum(aggr(if([Product Type]='Type-A',
sum({$<[Product SubType] = {"SubTypeA"}>} Quantity),
if([Product Type]='Type-B', sum({$<[Product SubType] = {"SubTypeB"}>} Quantity))), [Product Type]))
),
(
sum(aggr(
if([Product Type]='Type-A',
sum({$<[Product SubType] = {"SubTypeC"}>} Volume),
if([Product Type]='Type-B', sum({$<[Product SubType] = {"SubTypeD", "SubTypeB"}>} Quantity))), [Product Type ]))
)
)

It seems that the second 'Pick' is never calculated. If I replace the second aggr() with, for example, an integer, it shows fine. Likewise, if I remove the 'sum(aggr(...))' then I get a calculated value, however without the aggr() it is not correct. If I swap the two 'pick' parameters around, the function in the first position is always calculated correctly, however the second has no value.

Is there a limitation in QlikView that stops a second aggr() function from being used like this? Perhaps something incorrect in my syntax?

Thanks for taking a look, I'd appreciate if anyone can give me a bit of guidance on why this problem may be occurring.

[swuehl · Accepted solution]

You can look into using a NODISTINCT qualifier:

pick(
match(valuelist('Red','Blue'),'Red','Blue'),
(
sum(aggr(NODISTINCT if([Product Type]='Type-A',
sum({$<[Product SubType] = {"SubTypeA"}>} Quantity),
if([Product Type]='Type-B', sum({$<[Product SubType] = {"SubTypeB"}>} Quantity))), [Product Type]))
),
(
sum(aggr(NODISTINCT
if([Product Type]='Type-A',
sum({$<[Product SubType] = {"SubTypeC"}>} Volume),
if([Product Type]='Type-B', sum({$<[Product SubType] = {"SubTypeD", "SubTypeB"}>} Quantity))), [Product Type ]))
)
)

[#3]

Hi Swuehl,

Thanks for your answer! This worked perfectly. I ended up adding in the [Product ID] as one of the dimensions on the aggr() in both instances as well. With both these modifications my chart is now showing the expected results.

Thanks for the good advice!

[#4]

Hi Stefan,

I have same issue wherein my Second Pick is not getting calculated after applying filter

However, I dont have Sum function. I am trying to put all numbers Manually Product wise.

So when i apply filter on [TerritoryLevel4] ='S_UKI' it still shows value for [TerritoryLevel3]='S_EMEA'

Please help

E.g.

If([TerritoryLevel3]='S_EMEA',(Pick(Match([Product Sub Category],'A','B','C',

'D','E','F','G','H','I','J','K','L','M','N')+1,

7087645,3100720,7239302,3495407,11574307,1767474,5451370,4072107,9562232,3658490,4785699,322131,6208621,5825034,0))/1000000,

If([TerritoryLevel4] ='S_UKI',

(Pick(Match([Product Sub Category],'A','B','C',

'D','E','F','G','H','I','J','K','L','M','N')+1,

'1774348','655502','1518050','517787','2101909','-9183','3072889','853903','1993201','1183086','2501750','37068','1279540','1723476','0')/1000000),''))

[swuehl]

Not really sure if I understand your issue, so maybe it would be better if you create a new thread with your request.

In general, you need to test for finer granularity before lower granularity, hence if Territory level 4 is more granular than lever three, I think you should first test on level 4, then on level 3.

[#6]

You are Genius... It worked by changing sequence. Thanks a ton.

Just one more request. I amusing this measure in Pivot tables. i am getting value for each product but i am not getting totals any idea how can i get totals?

[swuehl]

Use Aggregation Functions!

[#8]

Hi Stefan,

may i please ask you to follow me back so that i can write to you directly.

Also where and which aggregation function should i use?

I tried sum function  before Pick(Match) in my expression and results are weird. Much higher.

Please suggest thanks

[Haarika]

Hello @swuehl,

I am using 2 aggregated expressions in pick function as give below and noticed that the 2nd aggr. expr. is not getting computed. Then as per your earlier post, I used NODISTINCT function after aggr. My observation is that the 2nd aggr expr now displays a value but it isnt accurate. could you please advise any other solution to overcome this problem?

pick(match(valuelist('Actuals','Budget','Balance To Go'),'Actuals','Budget','Balance To Go'),
sum(aggr(DISTINCT sum(distinct{<[Month Year]={'$(vMax_date_MY)'}>}[Dept Actuals]),[Document Number])),
sum({<[Month Year]={'$(vMax_date_MY)'}>}[Budget]),
(sum({<[Month Year]={'$(vMax_date_MY)'}>}[Budget])-sum(aggr(NODISTINCT sum(distinct{<[Month Year]={'$(vMax_date_MY)'}>}[Dept Actuals]),[Document Number])))
)

Many thanks,

Haarika 

[swuehl]

Haarika,

looks to me as if you are encountering issues with grain mismatch, have a look at 
https://community.qlik.com/t5/Qlik-Design-Blog/Pitfalls-of-the-Aggr-function/ba-p/1463275

Are you using this expression in a table with multiple dimensions?