Aggregating data

[s2016]

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

[sunny_talwar · Accepted solution]

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;

[s2016]

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.