Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Accumulation in the script level

Hi fellow qlikies,

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

Month YearRegionSales
Jan-14North100
Jan-14East50
Feb-14North20
Feb-14South40
Feb-14East30

New aggregated Table

Month YearRegionAggregated Sales
Jan-14North100
Jan-14East50
Feb-14North120
Feb-14South40
Feb-14East80

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

1 Solution

Accepted Solutions
MVP
MVP

Re: Accumulation in the script level

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.

5 Replies
agomes1971
Valued Contributor

Re: Accumulation in the script level

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

MVP
MVP

Re: Accumulation in the script level

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.

sergey_maka8
Valued Contributor

Re: Accumulation in the script level

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

sergey_maka8
Valued Contributor

Re: Re: Accumulation in the script level

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;

Not applicable

Re: Accumulation in the script level

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

Community Browser