Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
CUSTOMERS ONLY: Now accepting customer applications for the 2023 Luminary Program: SUBMIT NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Multiple Aggr() in Pick/Match function

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.

1 Solution

Accepted Solutions
swuehl
MVP
MVP

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

View solution in original post

10 Replies
swuehl
MVP
MVP

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

Not applicable
Author

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!

Not applicable
Author

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

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.

Not applicable
Author

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?

Not applicable
Author

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
Partner - Contributor III
Partner - Contributor III

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

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?