Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
Got a query on Range Sum (or Running Total). The following table shows sales by week by Product.
I would like to create a running total on each product by week and a sum of the total week to calculate the running total by product by week as a %.
The following table's last 3 columns is what I trying to achieve in Qlik formula and then put the table into a stacked bar to visualise so I can see the weekly running % on each product like the first screenshot attached below the table.
I've attached a second screenshot to shows the formula I used. I believe my first formula is working if the sorting is based on Product then Week #. However, the 'total by week' is not work especially when I select different product in a filter.
Week# | Product | Qty Sold | Product Running Total by Week | Total by Week | Column D / Column E |
1 | A | 2 | 2 | 10 | 20% |
1 | B | 5 | 5 | 10 | 50% |
1 | C | 3 | 3 | 10 | 30% |
2 | A | 10 | 12 | 30 | 40% |
2 | B | 5 | 10 | 30 | 33% |
2 | C | 5 | 8 | 30 | 27% |
3 | A | 10 | 22 | 60 | 37% |
3 | B | 10 | 20 | 60 | 33% |
3 | C | 10 | 18 | 60 | 30% |
4 | A | 22 | 60 | 37% | |
4 | B | 20 | 60 | 33% | |
4 | C | 18 | 60 | 30% | |
5 | A | 5 | 27 | 75 | 36% |
5 | B | 5 | 25 | 75 | 33% |
5 | C | 5 | 23 | 75 | 31% |
We will then visualise the table by stacked bar chart like below (product in different colour):
Here is the formula I used:
Please help.
Gavin
Hi @Gavin_FBu ! You can build this formula on Script.
Try:
Table1:
LOAD Week#,
Product,
if(len(trim([Qty Sold])) = 0, 0, [Qty Sold]) as [Qty Sold]
FROM
[Sales.xlsx]
(ooxml, embedded labels, table is Planilha1);
Left Join
Table2:
LOAD Week#, [Total Week], if(len(trim(Peek([Total by Week]))) = 0,0, Peek([Total by Week])) + [Total Week] as [Total by Week]
;
LOAD sum([Qty Sold]) as [Total Week], Week#
Resident Table1 Group By Week#;
NoConcatenate
Table2:
LOAD *,
if(Peek(Product) = Product, Peek([Product Running Total by Week]) + [Qty Sold], [Qty Sold]) as [Product Running Total by Week]
Resident Table1 Order by Product, Week#;
DROP Table Table1;
Result:
Hi @Gavin_FBu ! You can build this formula on Script.
Try:
Table1:
LOAD Week#,
Product,
if(len(trim([Qty Sold])) = 0, 0, [Qty Sold]) as [Qty Sold]
FROM
[Sales.xlsx]
(ooxml, embedded labels, table is Planilha1);
Left Join
Table2:
LOAD Week#, [Total Week], if(len(trim(Peek([Total by Week]))) = 0,0, Peek([Total by Week])) + [Total Week] as [Total by Week]
;
LOAD sum([Qty Sold]) as [Total Week], Week#
Resident Table1 Group By Week#;
NoConcatenate
Table2:
LOAD *,
if(Peek(Product) = Product, Peek([Product Running Total by Week]) + [Qty Sold], [Qty Sold]) as [Product Running Total by Week]
Resident Table1 Order by Product, Week#;
DROP Table Table1;
Result:
Hi PedroNetto,
The raw data is actually down to transaction data that included Customer, location details. Hence wonder if we can do a formula in Qlik instead of doing it at the load script level?
Cheers
Gavin
Hi @Gavin_FBu
Try for Total by Week:
SUM(TOTAL <Week#>
AGGR(rangesum(Above(
SUM(TOTAL <Week#> aggr(sum([Qty Sold]), Week#))
, 0, RowNo())), Week#)
)
Hi @PedroNetto
I now understand why you are working on the solution at the data load level instead of formula in Qlik. The running sum will not roll over if there is a week that has no sales.
Your formula is working but afraid the solution is the load script version. Thanks and I will study a bit more in the load script level.
Cheers