Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

quser_learn
New Contributor III

Current Day Sales vs. Previous Day Sales


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?

PcodeProduct_NameDateCumulative SalesDaily SalesDaily
  Cumulative Sales Change %
Daily Sales
  Change
Sales Lift
PROD1PROD1
  Name
1/2/201413051.081.000.04
PROD1PROD1
  Name
1/1/201412051.09-0.500.05
PROD1PROD1
  Name
12/31/2013110-100.852.00-0.08
PROD1PROD1
  Name
12/30/2013130-50.93-0.04
PROD1PROD1
  Name
12/29/2013140
PROD2PRD2
  NAME
1/2/201450-500.50-1.67-0.50
PROD2PRD2
  NAME
1/1/2014100301.43-0.430.43
PROD2PRD2
  NAME
12/31/201370-700.503.50-0.50
PROD2PRD2
  NAME
12/30/2013140-200.88-0.13
PROD2PRD2
  NAME
12/29/2013160
4 Replies
gopalopsharma
Contributor

Re: Current Day Sales vs. Previous Day Sales

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.

Highlighted
quser_learn
New Contributor III

Re: Current Day Sales vs. Previous Day Sales

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

gopalopsharma
Contributor

Re: Current Day Sales vs. Previous Day Sales

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.

quser_learn
New Contributor III

Re: Current Day Sales vs. Previous Day Sales

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