Range Sum () / Range Sum Total ()

[Gavin_FBu]

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

[PedroNetto · Accepted solution]

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:

[Gavin_FBu]

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

[PedroNetto]

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#)
)

[Gavin_FBu]

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