Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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 |
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.
Thanks Gopal - Can you let me know the load script syntax to calculate the three sales metrics for product & date combination?
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.
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