Announcements
You can succeed best and quickest by helping others to succeed. Join the conversation.
cancel
Showing results for
Did you mean:
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)) &lt; 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)) &lt; 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
Contributor II
Author

MVP

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?

17 Replies
MVP

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

Contributor II
Author
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))
MVP

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

Contributor II
Author
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.
MVP

@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

Contributor II
Author

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.

Contributor II
Author

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.

MVP

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

Contributor II
Author