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

[hic]

Try

Rolling:
Load
RangeSum(Sales,Peek(AccumulatedSales)) as AccumulatedSales,
*
Inline [
Fiscal Period,Sales
...

[vikasshana]

I tried but this is the output.

Fiscal Period	Sales	AccumulatedSales
202211	600	600
202212	550	1150
202301	100	1250
202302	200	1450
202303	300	1750
202304	400	2150
202305	500	2650
202306	600	3250
202307	700	3950
202308	800	4750
202309	900	5650
202310	1000	6650
202311	1100	7750
202312	1200	8950
202401	1300	10250
202402	1400	11650

where I'm expecting below where it ha to take only last 12 months numbers.

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

[marcus_sommer]

It could be reached with a small adjustment like:

RangeSum(Sales,Peek(AccumulatedSales) * sign(mod(rowno(), 12))) 

[vikasshana]

I tried it but still the same, can it be achieved with AsOfTable concept?

[marcus_sommer]

Yes, with an AsOfTable it should be also possible.

Just try the above again and also include each part of the additional suggestion as an own field, like:

sign(mod(rowno(), 12)) as X,
mod(rowno(), 12) as Y,
rowno() as Z

to comprehend the meaning.

Beside this such approach worked only within a correctly sorted resident-load - and if your real data contain further fields, like customers, products, channels and so on you will need to consider them with some if-loops as well as within the sorting. Also if there isn't a single sales value for a period else n ones which will probably require an appropriate group by load in beforehand.

[vikasshana]

I just tried your suggestion and received below output.

Fiscal Period	Sum(Sales)	Sum(AccumulatedSales1)	X	Y	Z
202211	600	600	1	1	1
202212	550	1150	1	2	2
202301	100	1250	1	3	3
202302	200	1450	1	4	4
202303	300	1750	1	5	5
202304	400	2150	1	6	6
202305	500	2650	1	7	7
202306	600	3250	1	8	8
202307	700	3950	1	9	9
202308	800	4750	1	10	10
202309	900	5650	1	11	11
202310	1000	1000		0	12
202311	1100	7750	1	1	13
202312	1200	8950	1	2	14
202401	1300	10250	1	3	15
202402	1400	11650	1	4	16

Would you able to share the script how it can be achieve by using AsOfTable.

[marcus_sommer]

Here is an excellent explanation for the as-of-table logic and how to do:

https://community.qlik.com/t5/Design/The-As-Of-Table/ba-p/1466130

[vikasshana]

I've checked this code, by using MonthDiff column we can write a Set Analysis to load last twelve months numbers, but how to restrict in the load script using as-of-table?

[marcus_sommer]

The as-of-table isn't aimed to restrict loads within the script else to provide a possibility to connect a dimension-value to multiple dimension-values of a parallel dimension, like having the current and previous period in the as-of-dimension and each one linked to 12 periods from the calendar. Then using the as-of-dimension within a chart and applying a sum() will return the accumulation of the 12 periods.

It's an additionally layer to simplified UI views without the need of pre-calculating / aggregation things in the script and/or to need set analysis and/or if-loops / aggr-constructs to create such views.