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

17 Replies
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

supriya_rangana
Contributor II
Contributor II
Author

Yes, but on selection of a month, the values need to first sum up based on month_lookback. Then a simple calculation needs to be performed, which has to be summed up based on the month.

Also the expression you posted is yielding 0 in a KPI object. 

Thank you once again.

sunny_talwar

I selected Month = 'Feb'... and I see this

image.png

What is the issue with the above screenshot?

supriya_rangana
Contributor II
Contributor II
Author

Hi Sunny, 

This has been achieved in a table but the same needs to be achieved in a KPI and Bar chart and also a table, but without making use of the month_lookback as a dimension. 

Please let me know if this can be achieved.

sunny_talwar

Have you looked at the screen shot my friend? the KPI object is showing 117... what is the problem?

supriya_rangana
Contributor II
Contributor II
Author

This works.. thank you so much... 

sunny_talwar

Lol okay... I guess you were not even trying my suggestions, right? All of sudden it went from doesn't work to work ;).

Best of luck,
Sunny

supriya_rangana
Contributor II
Contributor II
Author

I think I was rushing and didn't notice it correctly. But thank you so much.. You are awesome..