Announcements
The way to achieve your own success is the willingness to help somebody else. Go for it!
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

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

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.

MVP

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

What is the issue with the above screenshot?

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.

MVP

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

Contributor II
Author

This works.. thank you so much...

MVP

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

Contributor II
Author

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

Tags
Community Browser