Change the total type in expression

[ali_hijazi]

Hello
I got a table with 2 dimensions and several expressions
the expressions got aggregations and they are working well and giving correct results
there are two options given to the users to show on the chart either the count or the percentage

now what is required is that when the user clicks the percentage button, the total should display the average of the results and not the sum but when the count button is selected the total should display the sum

I tried to use the dimensionality() and check what the user selected as percentage or count, and added and Avg(my_expression)
I got an error for nested aggregation not allowed
is there a way to tackle this issue?

[lennart_mo]

Hi @ali_hijazi,

Qlik doesn't allow using an aggregation-function inside another aggregation normally, which is called nested aggregation.

You can bypass this by either using the TOTAL qualifier inside the inner aggregation or by using the AGGR()-Function.

Could you post your initial formula which gave you this error?

[Kushal_Chawda]

@ali_hijazi  let's say you have variable vType to switch % and sum, you could try below

if(Dimensionality()=0 and vType='Percentage',

avg(aggr(YourExpression,TableDimension1,TableDimension2)),

if(Dimensionality()=0 and vType='Sum',

YourSumExpression,

YourExpression

)
)

[ali_hijazi]

Hello @Kushal_Chawda 
I tried this approach but I got an error saying nested aggregation is not allowed
that's because YourExpression is a variable with aggregation too

what I did is I created two measures one with total function Count and another with total function avg
and each measure is hidden or visible depending on whether the user selected Count or Percentage