Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try
Rolling:
Load
RangeSum(Sales,Peek(AccumulatedSales)) as AccumulatedSales,
*
Inline [
Fiscal Period,Sales
...
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 |
It could be reached with a small adjustment like:
RangeSum(Sales,Peek(AccumulatedSales) * sign(mod(rowno(), 12)))
I tried it but still the same, can it be achieved with AsOfTable concept?
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.
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.
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
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?
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.