workaround for nested aggregation

[supriya_rangana]

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

[sunny_talwar]

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

[supriya_rangana]

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))

[sunny_talwar]

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]

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.

[sunny_talwar]

---

@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]

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]

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. 

[sunny_talwar]

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

[supriya_rangana · Accepted solution]

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