Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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
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.
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
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;
Thanks Jagan and Sergey for your help. You're true lifesavers