12 Month Rolling numbers in the script rather than front end

[vikasshana]

Hi,

I've the below.

Rolling:
Load * Inline [
Fiscal Period,Sales
202211,600
202212,550
202301,100
202302,200
202303,300
202304,400
202305,500
202306,600
202307,700
202308,800
202309,900
202310,1000
202311,1100
202312,1200
202401,1300

202402,1400
];

I'm looking for script in the backend to calculate the 12 Month number, expecting output as below.

Fiscal Period	Sales	12M Rolling Sales
202211	-	-
202212	-	-
202301	100	100
202302	200	300
202303	300	600
202304	400	1000
202305	500	1500
202306	600	2100
202307	700	2800
202308	800	3600
202309	900	4500
202310	1000	5500
202311	1100	6600
202312	1200	7800
202401	1300	9100
202402	1400	10400

I can achieve it in the front end by using RangeSum + above, but I want it to be implemented in the backend script so that I can store the 12 Month Rolling numbers in QVD and use the QVD in other app.

Regards,

V

[vikasshana]

got it, thanks for that. Any idea how to restrict the numbers at script level only?

[marcus_sommer]

The above shown examples with rangesum() and the interrecord-function peek() within a properly sorted resident load are suitable for such a task. Like hinted if there are more conditions you will need to consider them within the accumulation.

I suggest to create a dummy-application with a reduced and simplified data-set - maybe just a few periods + sales in an inline-load and accumulating just 3 months - and if you could comprehend the logic and get the expected results then you add another layer of complexity, like an extra dimension or n sales per period and extending the logic for an aggregation in beforehand and/or if-loops which query the previous dimension-values. like:

...
if(Field = previous(Field), then, else) as X
...

The general approach is quite simple and if there are n extra conditions you will need to include them, with more (nested) if-loops and/or some special flag-fields.

[WangKun]

Not an efficient way by using peek as below may meet your requirement. using join to calculate as of table may more efficient:

Rolling12:
Load
FiscalPeriod,
Sales,
Sales+ Coalesce(peek('Sales',-1),0)+Coalesce(peek('Sales',-2),0)+Coalesce(peek('Sales',-3),0)+
Coalesce(peek('Sales',-4),0)+Coalesce(peek('Sales',-5),0)+Coalesce(peek('Sales',-6),0)+
Coalesce(peek('Sales',-7),0)+Coalesce(peek('Sales',-8),0)+Coalesce(peek('Sales',-9),0)+
Coalesce(peek('Sales',-10),0)+Coalesce(peek('Sales',-11),0)+Coalesce(peek('Sales',-12),0) as RollingSales

resident Rolling
;
Drop tables Rolling