Aggr with if

[SwenWa]

Hi team,

is it possible to have an if-statement for the aggregation level?

I want to have Sales*Amount, aggregated by  either Derivate oder Class (depending of the user selection).

Something like this:

SUM(AGGR(Sales*Amount,   if (GetSelectedCount(Class)>0, Class, Derivate )))

Thanks Swen 🙂

[Anil_Babu_Samineni]

Perhaps this way?

If(GetSelectedCount(Class)>0, Sum({<Class=P(Class)>} (Sales*Amount), Sum({<Derivate=P(Derivate)>} (Sales*Amount))

[SwenWa]

Thanks! Yes this works, but it is not exactly what I was looking for. Is there bo possibility to have it in the aggr expression?

[rwunderlich · Accepted solution]

It's possible in the form you propose.  You need to calculate the Aggr dim as a Dollar Sign Expansion (DSE) like this:

SUM(AGGR(Sales*Amount, $(=if (GetSelectedCount(Class)>0, 'Class', 'Derivate'))))

Note the field returned from the if is a 'string'.

Because DSE is calculated once per chart, this works if you have a single condition for the entire chart.  If you needed to have your condition tested row by chart row you would need to write it like this:

if(somecondition, SUM(AGGR(Sales*Amount,   Class)),  SUM(AGGR(Sales*Amount,   Derivate)))

Your combination of Sum(Aggr()) didn't look logical to me, but I think that's irrelevant to the syntax question. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com