Accumulation in the script level

[#1]

Hi fellow qlikies,

I'm trying to simulate accumulation function that is available in chart properties but in the script level.

Month Year	Region	Sales
Jan-14	North	100
Jan-14	East	50
Feb-14	North	20
Feb-14	South	40
Feb-14	East	30

New aggregated Table

Month Year	Region	Aggregated Sales
Jan-14	North	100
Jan-14	East	50
Feb-14	North	120
Feb-14	South	40
Feb-14	East	80

The bold rows indicate the effect of aggregation. In other words, in Feb-2014 North - > it takes the sales from jan-2014 and adds it to the sales in feb-2014. Same should follow for march where it would take the new sales from feb-2014 North (120) and adds it to the value for Mar-2014 North.

Hope this makes sense. How can do this type of aggregation in QlikView?

Any suggestions should help and thanks for your time.

Regards

Vishal

[agomes1971]

Hi,

use a straight table with cumulated sum.

Sorry i don't have a personal edition right now but that's the way i can explain to you.

Regards

André Gomes

[jagan · Accepted solution]

Hi Vishal,

Try like this

LOAD
*,

If(Peek(Region) <> Region, Sales, Peek(AggregatedSales) + Sales) AS AggregatedSales

FROM DataSource

ORder by Region, MonthYear;

Hope this helps you.

Regards,

Jagan.

[SergeyMak]

Hi Vishal,

You should correct Jagan's script like

LOAD
*,

If(Peek('Region') <> Region, Sales, Peek('AggregatedSales') + Sales) AS AggregatedSales

FROM DataSource

Order by Region, MonthYear;

Peek function doesn't work without quotes

Regards,

Sergey

[SergeyMak]

Try the following script and PFA

tmp:
LOAD [Month Year],
      Region,
      Sales
FROM
[http://community.qlik.com/thread/142875]
(html, codepage is 1252, embedded labels, table is @1);


Data:
LOAD
*,
If(Peek('Region') <> Region, Sales, Peek('AggregatedSales') + Sales) AS AggregatedSales
Resident tmp
Order by Region, [Month Year];

Drop table tmp;

[#6]

Thanks Jagan and Sergey for your help. You're true lifesavers