topic Re: Current Day Sales vs. Previous Day Sales in QlikView

Current Day Sales vs. Previous Day Sales

Anonymous — Mon, 20 Jan 2014 16:59:06 GMT

I have a table in the below format and would like to calcuate the following sales metrics

Daily Sales Change = Current Day Cumulative Sales - Previous Day Cumulative Sales

Daily Cumuative Sales Change = Current Day Cumulative Sales \ Previous Day Cumulative Sales

Sales Lift = Daily Sales/ Previose Day Cumulative Sales

I have the data until Daily Sales loaded in a table. Is it good idea to calcuate in a script or in expression?

Pcode	Product_Name	Date	Cumulative Sales	Daily Sales	Daily Cumulative Sales Change %	Daily Sales Change	Sales Lift
PROD1	PROD1 Name	1/2/2014	130	5	1.08	1.00	0.04
PROD1	PROD1 Name	1/1/2014	120	5	1.09	-0.50	0.05
PROD1	PROD1 Name	12/31/2013	110	-10	0.85	2.00	-0.08
PROD1	PROD1 Name	12/30/2013	130	-5	0.93		-0.04
PROD1	PROD1 Name	12/29/2013	140
PROD2	PRD2 NAME	1/2/2014	50	-50	0.50	-1.67	-0.50
PROD2	PRD2 NAME	1/1/2014	100	30	1.43	-0.43	0.43
PROD2	PRD2 NAME	12/31/2013	70	-70	0.50	3.50	-0.50
PROD2	PRD2 NAME	12/30/2013	140	-20	0.88		-0.13
PROD2	PRD2 NAME	12/29/2013	160

Re: Current Day Sales vs. Previous Day Sales

gopalopsharma — Mon, 20 Jan 2014 17:08:10 GMT

Hello Learn,

It is good practice to do the static calculation in script or on the data itself if you are using some tool for ETL (e.g. SAS).

Since Qlikview calculates every expression every time when user makes some selection in the report, if we write the expression then QV will be evaluating the expressions every time. But if we do this in the script then QV just needs to pull the values.

But in the above case there is one caveat that if you need to roll up the data and see at some higher level (e.g Market) then doing in script will not help.

Re: Current Day Sales vs. Previous Day Sales

Anonymous — Mon, 20 Jan 2014 17:19:48 GMT

Thanks Gopal - Can you let me know the load script syntax to calculate the three sales metrics for product & date combination?

Re: Current Day Sales vs. Previous Day Sales

gopalopsharma — Mon, 20 Jan 2014 17:26:47 GMT

Hello Learn,

It will the same as we are doing in the expression:

LOAD

([Current Day Cum Sales]-[Previous Day Cum Sales]) as DailySalesChange

[Current Day Cum Sales]/[Previous Day Cum Sales] as DailyCumSalesChange

and so on...

Hope this helps.

Re: Current Day Sales vs. Previous Day Sales

Anonymous — Mon, 20 Jan 2014 17:33:14 GMT

Gopal,

The last three colums are not in the table. (I just calucated using excel for reference purposes.)

I want to calculate the following using the first 5 columns.

Note - if the previous day is sun or sat, calculation should use friday's value.

Daily Sales Change = Current Day Cumulative Sales - Previous Day Cumulative Sales

Daily Cumuative Sales Change = Current Day Cumulative Sales \ Previous Day Cumulative Sales

Sales Lift = Daily Sales/ Previose Day Cumulative Sales