Current Day Sales vs. Previous Day Sales

[Anonymous]

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

[gopalopsharma]

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.

[Anonymous]

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

[gopalopsharma]

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.

[Anonymous]

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