Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
The source table is as follows:
I want to add a new field which will calculate the sales of last two months based on the dimensins.
So the new table will look like below.
Here I want the calculation to be done in the Qlikview Script only.
Use the following script:
Table:
LOAD *,
DIM1&DIM2 as Combo;
LOAD * INLINE [
DIM1, DIM2, Month, Sales
A, xx, 20140901, 10
A, yy, 20140901, 20
A, xx, 20141001, 30
A, yy, 20141001, 40
A, xx, 20141101, 50
A, yy, 20141101, 60
A, xx, 20141201, 70
A, yy, 20141201, 80
A, xx, 20150101, 90
A, yy, 20150101, 10
A, xx, 20150102, 20
A, yy, 20150102, 30
];
Table1:
LOAD Combo,
DIM1,
DIM2,
Month,
Sales,
RangeSum(Alt(Peek('Sales'), 0)) + RangeSum(Alt(Peek('Sales', -2), 0)) as [Last 2 Months]
Resident Table
Where Combo = 'Axx';
Table2:
NoConcatenate
LOAD Combo,
DIM1,
DIM2,
Month,
Sales,
RangeSum(Alt(Peek('Sales'), 0)) + RangeSum(Alt(Peek('Sales', -2), 0)) as [Last 2 Months]
Resident Table
Where Combo = 'Ayy';
DROP Table Table;
Table:
NoConcatenate
LOAD *
Resident Table1;
Concatenate(Table)
LOAD *
Resident Table2;
DROP Table Table1, Table2;
x:
LOAD * INLINE [
DIM1, DIM2, Month, Sales
A, xx, 20140901, 10
A, yy, 20140901, 20
A, xx, 20141001, 30
A, yy, 20141001, 40
A, xx, 20141101, 50
A, yy, 20141101, 60
A, xx, 20141201, 70
A, yy, 20141201, 80
A, xx, 20150101, 90
A, yy, 20150101, 10
A, xx, 20150102, 20
A, yy, 20150102, 30
];
y:
load
*,
if(DIM1=peek(DIM1) and DIM2=peek(DIM2), RangeSum(Alt(Peek('Sales'), 0)),0)
+
if(DIM1=peek(DIM1,-2) and DIM2=peek(DIM2,-2), RangeSum(Alt(Peek('Sales', -2), 0)),
0) as [Last 2 Months]
Resident x
order by DIM1, DIM2, Month;
DROP Table x;
Massimo Grossi this is a old post from May 5th, not sure why old posts are coming up randomly.