Calculating Last two months sales in Script Level - Qlik Community

Vinothishere · ‎2015-05-10

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

Anonymous · ‎2015-05-10

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

sunny_talwar · ‎2015-05-10

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

maxgro · ‎2015-05-10

SET DateFormat='DD-M-YYYY';

q:

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;

Report Inappropriate Content · ‎2015-05-10

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?