topic Re: Not recalculate when limit dimension! in QlikView

Not recalculate when limit dimension!

Fri, 21 Jun 2013 13:31:45 GMT

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!

Re: Not recalculate when limit dimension!

Fri, 21 Jun 2013 13:36:47 GMT

or try this

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

Re: Not recalculate when limit dimension!

Fri, 21 Jun 2013 13:47:06 GMT

Unfortunately not.

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

Re: Not recalculate when limit dimension!

Gysbert_Wassenaar — Fri, 21 Jun 2013 14:02:22 GMT

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])

Re: Not recalculate when limit dimension!

Fri, 21 Jun 2013 15:39:44 GMT

Does not work.

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

Thanks.

Re: Not recalculate when limit dimension!

Gysbert_Wassenaar — Fri, 21 Jun 2013 17:07:08 GMT

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.

Re: Not recalculate when limit dimension!

Fri, 28 Jun 2013 17:20:11 GMT

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))