Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
supriya_rangana
Contributor II
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
Contributor II
Contributor II
Author

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

View solution in original post

sunny_talwar

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
sunny_talwar

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

image.png

supriya_rangana
Contributor II
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))
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
Contributor II
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.
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
Contributor II
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.

 

supriya_rangana
Contributor II
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. 

sunny_talwar

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

supriya_rangana
Contributor II
Contributor II
Author

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