Above with inner aggr - ignores inner selection

[marek_polasek]

Let's have simple model: Month, Product, Sales.

The task is to create a table (or graph) showing for each month the sum of sales for months M-2, M-3, M-4.
 For example: on June-2017 we want to show
 sum of sales for three months (April-2017, March-2017, February-2017).
The chart shall reflect our selection in month and products. This means that if we select only June-2017, three month data (which is not selected) should be taken for calculation.

I tried to solve this by expression:

Sum(Aggr(RangeSum(Above(Aggr(sum({$<Month=>}Sales),Product,Month),2,3)), Product, Month))

which gives correct result but takes data for calculation only from selected months.

[vishsaggi]

‌mmay be try like

Sum({<Month>} Aggr(RangeSum(Above(Aggr(sum({$<Month=>}Sales),Product,Month),2,3)), Product, Month))

[sebastiandperei]

Hi!

Create one Alternate State (Document Options, General, Alternate states). We will call it "aux" in the example.

- First, you need to duplicate Month listbox and asign to the copy the "aux" state, then clear selection and then erase the copy

- Now, asign "aux" state to the graph

- Edit the Month dimention. It must be: =aggr(only({$}Month),Month)

- The expression must be: Sum(Aggr(RangeSum(Above(Aggr(sum({sss}Sales),Product,Month),2,3)), Product, Month))

[marek_polasek]

Adding Month to outer expression has no effect.

[marek_polasek]

Sebastian,

thank you for your response. However your suggestion does not work. I am affraid you did not try it.

[sebastiandperei]

Yes Marek, i have tested and it worked.

Im affraid YOU haven't did it conscientiously. I was made a mistake in expression in the name of the state.

[sunny_talwar · Accepted solution]

Is this what you are trying to get?

May be use this

Sum(Aggr(RangeSum(Above(Sum({$<Month>}Sales), 2, 3)), Product, Month))

[marek_polasek]

Thank you, I'd rather prefer the solution of Sunny Talwar. It works with selection in other dimensions (like selecting Product) without any complications in alternative states.

M.