Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

supriya_rangana
New Contributor II

workaround for nested aggregation

Hi All,
We have a requirement where we need to calculate a dynamic value after aggregation conditionally based on selection, and again aggregate the output. Here is the expression:
If(avg(aggr(sum(fld1),month1,month2)) < avg(aggr(sum(fld2),month1,month2)),
Aggr(avg(fld3),month1,month2),
Aggr(avg(fld1),month1,month2)
)
This expression gives us individual month2 sum aggregation results. We want an aggregation on top of this for every month1.
Something like this:
Sum(aggr(
If(avg(aggr(sum(fld1),month1,month2)) < avg(aggr(sum(fld2),month1,month2)),
Aggr(avg(fld3),month1,month2),
Aggr(avg(fld1),month1,month2)
)
,Month1))
We get 0 when we do this. Kindly help

2 Solutions

Accepted Solutions
supriya_rangana
New Contributor II

Re: workaround for nested aggregation

Please find the .qvf attached. Thanks for all your help.

Re: workaround for nested aggregation

From what I see that you have made your expression extremely complex which can be resolved to just this for table or kpi object

=Sum(Aggr(If(Sum([Residual Receivables]) >= Sum(Sales),
	Avg([#Days]),
	Avg([Residual Receivables])/Avg(Sales)*Avg([#Days]))
, Month,Month_Lookback))

Is this what you wanted to get?

image.png

17 Replies

Re: workaround for nested aggregation

I have never seen this... what exactly is this in your expression

image.png

Highlighted
supriya_rangana
New Contributor II

Re: workaround for nested aggregation

Sum(aggr(
If(avg(aggr(sum(fld1),month1,month2)) < avg(aggr(sum(fld2),month1,month2)),
Aggr(avg(fld3),month1,month2),
Aggr(avg(fld1),month1,month2)
)
,Month1))

Re: workaround for nested aggregation

This make sense... what is the issue with it? Can you share more details and may be a sample to help you better 🙂

supriya_rangana
New Contributor II

Re: workaround for nested aggregation

Here we see the aggregation applied on the inner if statement by month1. But the result if that is 0 and this is the issue.

Re: workaround for nested aggregation


@supriya_rangana wrote:
Here we see the aggregation applied on the inner if statement by month1. But the result if that is 0 and this is the issue.

Hahahaha okay... I guess you cannot really share more information... but with what you are providing, I might not be able to help.

I hope someone else will be able to chip in.

Best of luck,
Sunny

supriya_rangana
New Contributor II

Re: workaround for nested aggregation

Hi Sunny,

Sorry I was not able to be clear earlier. Here is the sample file and the .qvf file for your reference. The excel file is the sample data file. There are 2 level of month columns. We first need to sum the measures up based on Month2, make a calculation on the summed up values, and then need to sum up the resulting calculation . Please let me know if this is feasible in Qlik.

 

supriya_rangana
New Contributor II

Re: workaround for nested aggregation

I was able to arrive at the numbers with the help of a table. But the result is expected to appear in KPI object , table without using Month2 as a dimension and bar chart with rolling 12 months. I am not able to attach the .qvf file. 

Re: workaround for nested aggregation

I don't see a qvf file attached.... Did you zip your qvf before trying to attach it?

supriya_rangana
New Contributor II

Re: workaround for nested aggregation

Please find the .qvf attached. Thanks for all your help.