topic Re: Accumulation in the script level in QlikView

Accumulation in the script level

Thu, 20 Nov 2014 01:11:24 GMT

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

Re: Accumulation in the script level

agomes1971 — Thu, 20 Nov 2014 01:31:25 GMT

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

Re: Accumulation in the script level

jagan — Thu, 20 Nov 2014 01:43:48 GMT

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.

Re: Accumulation in the script level

SergeyMak — Thu, 20 Nov 2014 02:01:14 GMT

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

Re: Re: Accumulation in the script level

SergeyMak — Thu, 20 Nov 2014 02:06:55 GMT

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;

Re: Accumulation in the script level

Thu, 20 Nov 2014 03:28:41 GMT

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