Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
Highlighted
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.

View solution in original post

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

View solution in original post

17 Replies

Re: workaround for nested aggregation

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

image.png

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.

View solution in original post