topic Re: Sum last 12 months - moving average in QlikView

Sum last 12 months - moving average

petermoyaert — Wed, 25 Nov 2020 16:16:04 GMT

Hello all,

I succeed in calculating the sales of the last 12 months but if I want this calculation in a table for each month it does not work.

So in the month september I want to see sales of the last 12 months, starting from september, In the month august I want to see sales of the last 12 months, starting from August and so on.

This is the formula I used to calculate sales of the last 12 months

=sum({$<Jaar_Kalender=, Maand_Kalender=, YTD_Flag=, %Key_Datum={'>=$(=vStartDate)<=$(=vMaxDate)'},Eigen_Merk={'Nee'}>} Waarde_Levering)

where vStartDate=date(Today()-365,'DD/MM/YYYY')

where vMaxdate=max(%Key_Datum)

As soon as I add a dimension Month-Year I get sales per month and not the cumul of the previous 12 months...

Can someone help ?

Peter

Re: Sum last 12 months - moving average

Anil_Babu_Samineni — Thu, 14 Sep 2017 09:55:03 GMT

If it is straight table then you can use Accumulate radio button or else try this

Rangesum(Below(sum({$<Jaar_Kalender=, Maand_Kalender=, YTD_Flag=, %Key_Datum={'>=$(=vStartDate)<=$(=vMaxDate)'},Eigen_Merk={'Nee'}>}Waarde_Levering),0),Above(Waarde_Levering,1, RowNo(TOTAL)))

Re: Sum last 12 months - moving average

petermoyaert — Thu, 14 Sep 2017 11:22:07 GMT

It does not seem to work.

I still get only the sales of the month :

Where I should have the total of 23.266.764 in sep-2017

Peter

Re: Sum last 12 months - moving average

mdmukramali — Thu, 14 Sep 2017 11:32:23 GMT

Dear Peter,

Can you Try

=RangeAvg(Above(Sum(Waarde_Levering), 0, 12 ))

=RangeSum(Above(Sum(Waarde_Levering), 0, 12 ))

Re: Sum last 12 months - moving average

sunny_talwar — Thu, 14 Sep 2017 11:36:12 GMT

Or try this

=RangeSum(Above(Sum({$<Jaar_Kalender=, Maand_Kalender=, MaandJaar_Kalender=, YTD_Flag=, %Key_Datum=,Eigen_Merk={'Nee'}, >} Waarde_Levering), 0, RowNo())) * Avg(1)