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 · Accepted solution]

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?

[supriya_rangana]

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

What is the issue with the above screenshot?

[supriya_rangana]

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]

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]

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