topic Cumulative calculation script in QlikView

Cumulative calculation script

hardleers — Sat, 16 Nov 2024 01:21:48 GMT

Hello all.

I'm looking to incorporate a cumulative average in the script.

My table looks in a very basic way like:

Customer	Article	Date	Sales_date	Shipmentreceived_date	QtySold	QtyReceived
A	A	01-01-20		01-01-20		5
A	A	05-01-20	05-01-20		2
A	A	10-01-20	10-01-20		1
A	A	16-01-20		16-01-20		3
A	A	18-01-20	18-01-20		4
B	A	01-01-20		01-01-20		5
B	A	04-01-20	04-01-20		3
A	B	01-01-20		01-01-20		10
B	B	02-01-20		02-01-20		15

I'm trying to calculate the average stock level per date. The result should be something like this:

Customer	Article	Date	Sales_date	Shipmentreceived_date	QtySold	QtyReceived	QtySold_Cumul	QtyReceived_Cumul	Qty_stock_on_date
A	A	01-01-20		01-01-20		5		5	5
A	A	05-01-20	05-01-20		2		2	5	3
A	A	10-01-20	10-01-20		1		3	5	2
A	A	16-01-20		16-01-20		3	3	8	5
A	A	18-01-20	18-01-20		4		7	8	1
B	A	01-01-20		01-01-20		3		3	3
B	A	04-01-20	04-01-20		2		2	3	1
A	B	01-01-20		01-01-20		10		10	10
B	B	02-01-20		02-01-20		15		15	15

So I would like to add rows for cumulative sales per customer per article and cumulative shipments received per customer per article. These should be subtracted from each other defined by a time period (per date, or maybe week(date)). So I can get a stock average per week. So if I check the stock for 11-01-20 for customer A and article A it will show a stock level of 3.

Is there a simple way of doing this?

Re: Cumulative calculation script

sunny_talwar — Tue, 04 Feb 2020 12:55:32 GMT

Where is Sales_date 18-01-20 with QtySold of 4 coming from? I don't see an entry within the input table?

Re: Cumulative calculation script

hardleers — Tue, 04 Feb 2020 16:13:44 GMT

Good noticed. These tables stay annoying layout wise. Updated for visibility. But do you have an idea?

Re: Cumulative calculation script

Brett_Bleess — Tue, 03 Mar 2020 20:41:51 GMT

Have a look at the following Design Blog post, not sure if it may help or not:

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

https://community.qlik.com/t5/Qlik-Design-Blog/Average-Which-average/ba-p/1466654

https://community.qlik.com/t5/Qlik-Design-Blog/Set-Analysis-in-the-Aggr-function/ba-p/1463822

If those fail, you can use the following link to search further yourself to see if anything you find may be better...

https://community.qlik.com/t5/Qlik-Design-Blog/bg-p/qlik-design-blog

Sorry I do not have something better for you.

Regards,
Brett