topic Re: Calculating Last two months sales in Script Level in QlikView

Calculating Last two months sales in Script Level

Vinothishere — Sun, 10 May 2015 16:41:01 GMT

Hi All,

I have the following sample data

Dim1 Dim2 Date Sales

A 1 1-1-2015 100

A 1 1-2-2015 200

A 1 1-3-2015 200

A 2 1-1-2015 150

A 2 1-2-2015 200

A 2 1-3-2015 500

A 2 1-4-2015 150

B 1 1-1-2015 150

B 1 1-2-2015 300

B 1 1-3-2015 400

B 1 1-4-2015 200

I need to calculate the last two months sales and load it as a new field like below:

Dim1 Dim2 Date Sales Last 2 Months Sales

A 1 1-1-2015 100 0

A 1 1-2-2015 200 0

A 1 1-3-2015 200 300

A 2 1-1-2015 150 0

A 2 1-2-2015 200 0

A 2 1-3-2015 500 350

A 2 1-4-2015 150 700

B 1 1-1-2015 150 0

B 1 1-2-2015 300 0

B 1 1-3-2015 400 450

B 1 1-4-2015 200 700

Thanks in advance

Re: Calculating Last two months sales in Script Level

Anonymous — Sun, 10 May 2015 17:25:47 GMT

Hi Vinoth,

You could create two columns with the previous two columns dates, and use this date to left join the original table to get the sales from both dates, and then add them.Please refer to the attached file.

Hope it helps.

Karla

Re: Calculating Last two months sales in Script Level

sunny_talwar — Sun, 10 May 2015 17:36:16 GMT

Try this script:

Table:

LOAD *,

Dim1&Dim2 as Join;

LOAD * INLINE [

Dim1, Dim2, Date, Sales

A, 1, 01/01/2015, 100

A, 1, 01/02/2015, 200

A, 1, 01/03/2015, 200

A, 2, 01/01/2015, 150

A, 2, 01/02/2015, 200

A, 2, 01/03/2015, 500

A, 2, 01/04/2015, 150

B, 1, 01/01/2015, 150

B, 1, 01/02/2015, 300

B, 1, 01/03/2015, 400

B, 1, 01/04/2015, 200

];

Table2:

LOAD Join,

Dim1,

Dim2,

Date,

Sales,

If(Peek('Join') = Join and Peek('Join', -2) = Join, Peek('Sales') + Peek('Sales', -2)) as [Last 2 Month Sales]

Resident Table;

DROP Table Table;

HTH

Best,

Sunny

Re: Calculating Last two months sales in Script Level

maxgro — Sun, 10 May 2015 17:52:44 GMT

SET DateFormat='DD-M-YYYY';

load * inline [

Dim1 , Dim2 , Date , Sales

A , 1 , 1-1-2015, 100

A , 1 , 1-2-2015 , 200

A , 1 , 1-3-2015 , 200

A , 2 , 1-1-2015 , 150

A , 2 , 1-2-2015 , 200

A , 2 , 1-3-2015 , 500

A , 2 , 1-4-2015 , 150

B , 1 , 1-1-2015 , 150

B , 1 , 1-2-2015 , 300

B , 1 , 1-3-2015 , 400

B , 1 , 1-4-2015 , 200

];

t: load *,

RangeSum(Peek(Sales),Peek(Sales, -2)) as Last2MonthSales,

if(Dim1<>Peek(Dim1) or Dim2<>Peek(Dim2), 1, Peek(Counter)+1) as Counter

Resident q

order by Dim1,Dim2,Date;

DROP Table q;

z: NoConcatenate load Dim1, Dim2, Date, Sales,

if(Counter>2, Last2MonthSales, 0) as Last2MonthSales

Resident t;

DROP Table t;

Re: Calculating Last two months sales in Script Level

Sun, 10 May 2015 17:53:35 GMT

Hi,

think you've prob got the solution to your problem from one of the responses. Was just curious to know what will you be using the 2 month sales field for?