CODEWITHSUNDEEP
excelalgorithmaggregate-functionspivot-tableqlikview
kiriloff
kiriloff

Reputation: 26333

QLikView calculated dimension with aggregation w.r.t. other dimension in pivot-table

In a pivot table, I have one calculated dimension with complex expression:

pick(1+match(IF([Corporate Account]='OTHER','[Sales Account]', 
'[Corporate Account]'),...,...,...),...,...,...,...)

In another dimension, I want to sum revenu with respect to this first dimension. More precisely, I want to have value for this new calculated dimension be 'A' or 'B' depending on the total revenue with respect to the first field.

So far, I came up with

=AGGR( IF(Sum(Revenue)>10,'A','B'),
$(=pick(1+match(IF([Corporate Account]='OTHER','[Sales Account]', 
'[Corporate Account]'),...,...,...),...,...,...,...)))

and this is not correct (for all rows, output is Null()).

Would you have an idea of what is the right syntax ?

Many thanks in advance

Upvotes: 0

Views: 2251

Answers (1)

i_saw_drones
i_saw_drones

Reputation: 3506

Without seeing your full dimension expression it is not that easy to diagnose the issue, however, I tried to recreate your expression:

=aggr(if(sum(Revenue)>10,'A','B'),
$(=pick(match(IF([Corporate Account]='OTHER','[Sales Account]', '[Corporate Account]'),
'[Corporate Account]', '[Sales Account]'),'[Sales Account]', '[Corporate Account]')))

I noticed that if I put the dimensions in the pick() entries in quotes then the expression is evaluated fine since pick() would return a quoted value, e.g. '[Sales Account]' which is then evaluated by $() to [Sales Account] and so becomes a "real" dimension which aggr() can use as an aggregation dimension.

If this doesn't work, please could you add your full expression?

Upvotes: 1

Related Questions