Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
we are calculating distinct values using Aggr. If we apply NoDistinct, The values are getting changed. Is there any alternative method to sort it?