Not recalculate when limit dimension!

[#1]

Hi,

The following expression makes a division of 2 fields and it works fine.

rangesum(above(sum(GASTO_CALC),0,12))
/
rangesum(above(sum(RECEITA_CALC),0,12))

The problem is that I need to display only the last 12 values ​​and when I determine a limit of the dimension, the values ​​are changed.

I believe this occurs because of 0 (first line) at ,0,12.

What I want is NOT RECALCULATE values ​​when I display only the last 12 in the chart.

Can You help me?

Thanks!

[#2]

or try this

rangesum(above(sum(GASTO_CALC),9,rowno()))
/
rangesum(above(sum(RECEITA_CALC),9,rowno()))

[#3]

Unfortunately not.

-12 displays nothing and 9 (also tried 12) still display the wrong values​​.

[Gysbert_Wassenaar]

Maybe this:

aggr(rangesum(above(sum({<[Ano-Mês]=>}GASTO_CALC),0,12))
/
rangesum(above(sum({<[Ano-Mês]=>}RECEITA_CALC),0,12)),[Ano-Mês])

[#5]

Does not work.

Anyway, I'm attaching the file in first post.

Thanks.

[Gysbert_Wassenaar · Accepted solution]

See attached example. The aggr-rangesum combination needs ANO_MES to be sorted chronologically. This is because the aggr function doesn't have a sort parameter and thus uses the load order. If you first load the months and make sure they're sorted by chronologically the aggr-rangesum expression works fine.

You could also try an AsOf table. See Calculating rolling n-period totals, averages or other aggregations for more information.

[#7]

Thanks Gysbert,

The problem was solved using just set analysis.

Looking at your expression, I figure out how to use it properly.

sum(aggr(rangesum(above(total sum({<ANO_MES,ANO,MÊS,ORDEM_MES=>}GASTO_CALC),0,12)),ANO_MES,ANO,MÊS,ORDEM_MES))
/
sum(aggr(rangesum(above(total sum({<ANO_MES,ANO,MÊS,ORDEM_MES=>}RECEITA_CALC),0,12)),ANO_MES,ANO,MÊS,ORDEM_MES))