topic Re: Aggregating data in QlikView

Aggregating data

s2016 — Thu, 25 May 2017 07:39:56 GMT

Hi,

I have the attached sample data set. For each combination of "Group Name" and "EST_Date", I need to calculate the Maximum of "Peak" value. Peak for current row is derived from Average ("Throughput" current row, Throughput next row).

eg)

Peak row1 = average of Throughput (row1, row2)

Peak row2 = average of Throughput (row2, row3)

so on and so forth.

I am not able to figure out how the data for the next row can be fetched in Qlikview so that it can be averaged with the current row. Help in scripting this would be really appreciated.

NOTE: Peak value is not available in the actual dataset which comes from a database.

Derived
Group Name	EST Date	Time Slot Number	Throughput	Peak
BRAS	21/01/2008	1	152256	143292
BRAS	21/01/2008	2	134328	130238.5
BRAS	21/01/2008	3	126149	118795
BRAS	21/01/2008	4	111441	106408
BRAS	21/01/2008	5	101375	100557.5
BRAS	21/01/2008	6	99740	97986
BRAS	21/01/2008	7	96232	93780.5
BRAS	21/01/2008	8	91329	91329
				143292	Max

Re: Aggregating data

sunny_talwar — Thu, 25 May 2017 18:18:34 GMT

You are looking to do this in the script?

May be this:

Table:

LOAD [Group Name],

[EST Date],

[Time Slot Number],

Throughput

FROM

[..\..\Downloads\Rubas-Peak-Mbits_Calculations_copy.xlsx]

(ooxml, embedded labels, header is 1 lines, table is Sheet1)

Where Len(Trim([Group Name])) > 0;

FinalTable:

LOAD *,

If([Group Name] = Previous([Group Name]) and [EST Date] = Previous([EST Date]), RangeAvg(Peek('Throughput'), Throughput), Throughput) as Peak

Resident Table

Order By [Group Name], [EST Date], [Time Slot Number] desc;

DROP Table Table;

Re: Aggregating data

s2016 — Fri, 26 May 2017 05:34:34 GMT

Thanks for the solution Sunny. It didn't work perfectly, but I did some tweaks and I got the desired results. Appreciate your help. Thanks again.