topic Re: Change the total type in expression in App Development

Change the total type in expression

ali_hijazi — Tue, 23 Jul 2024 07:16:07 GMT

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?

Re: Change the total type in expression

lennart_mo — Tue, 23 Jul 2024 12:53:30 GMT

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?

Re: Change the total type in expression

Kushal_Chawda — Tue, 23 Jul 2024 15:16:21 GMT

@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

)
)

Re: Change the total type in expression

ali_hijazi — Wed, 24 Jul 2024 07:35:17 GMT

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