topic 12 Month Rolling numbers in the script rather than front end in QlikView

12 Month Rolling numbers in the script rather than front end

vikasshana — Fri, 08 Mar 2024 11:51:35 GMT

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,

Re: 12 Month Rolling numbers in the script rather than front end

hic — Fri, 08 Mar 2024 11:38:18 GMT

Try

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

Re: 12 Month Rolling numbers in the script rather than front end

vikasshana — Fri, 08 Mar 2024 11:52:09 GMT

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

Re: 12 Month Rolling numbers in the script rather than front end

marcus_sommer — Fri, 08 Mar 2024 13:03:59 GMT

It could be reached with a small adjustment like:

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

Re: 12 Month Rolling numbers in the script rather than front end

vikasshana — Fri, 08 Mar 2024 13:39:20 GMT

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

Re: 12 Month Rolling numbers in the script rather than front end

marcus_sommer — Fri, 08 Mar 2024 14:43:49 GMT

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.

Re: 12 Month Rolling numbers in the script rather than front end

vikasshana — Fri, 08 Mar 2024 18:30:35 GMT

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.

Re: 12 Month Rolling numbers in the script rather than front end

marcus_sommer — Sun, 10 Mar 2024 12:16:34 GMT

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

Re: 12 Month Rolling numbers in the script rather than front end

vikasshana — Sun, 10 Mar 2024 18:46:15 GMT

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?

Re: 12 Month Rolling numbers in the script rather than front end

marcus_sommer — Mon, 11 Mar 2024 07:42:46 GMT

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.

Re: 12 Month Rolling numbers in the script rather than front end

vikasshana — Mon, 11 Mar 2024 09:58:30 GMT

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

Re: 12 Month Rolling numbers in the script rather than front end

marcus_sommer — Mon, 11 Mar 2024 10:42:33 GMT

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.

Re: 12 Month Rolling numbers in the script rather than front end

WangKun — Sat, 11 May 2024 09:01:32 GMT

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