topic Re: Aggr function ? I need to make a ranking in QlikView

Aggr function ? I need to make a ranking

jumiprado — Mon, 22 Oct 2018 13:17:02 GMT

Hey guys!

I have the following table

COMPANY	AREA	SUB AREA	PERIOD	DAYS
COMPANY A	AREA 1	SUB_A1	JAN-18	10
COMPANY A	AREA 1	SUB_A1	FEB-18	15
COMPANY A	AREA 2	SUB_A2	JAN-18	5
COMPANY A	AREA 2	SUB_A2	FEB-18	20
COMPANY A	AREA 3	SUB_A3	JAN-18	30

I need to make a pivot table that makes a ranking with top 10 with days but calculating that grouped for the dimensions

I hope you understand my poor english!

Thanks!

stalwar1‌

swuehl‌

Re: Aggr function ? I need to make a ranking

sunny_talwar — Mon, 22 Oct 2018 13:28:44 GMT

What is the expected output needs to look like?

Re: Aggr function ? I need to make a ranking

Anil_Babu_Samineni — Mon, 22 Oct 2018 13:31:34 GMT

Not sure i understand the business, But this will help better?

SUM({<COMPANY = {"=Rank(SUM({<DATE = {">=$(=Date(Max(DATE-10)))<=$(=Date(Max(DATE)))"}>} DAYS))<=10"}, DATE = {">=$(=Date(Max(DATE-10)))<=$(=Date(Max(DATE)))"}>}DAYS)

Dim1 =IF(Aggr(Rank(SUM(DAYS), COMPANY)<=10, COMPANY)

Dim2 =IF(Aggr(Rank(SUM(DAYS), AREA)<=10, AREA)

Dim3 =IF(Aggr(Rank(SUM(DAYS), [SUB AREA])<=10, [SUB AREA])

Dim4 =IF(Aggr(Rank(SUM(DAYS), PERIOD)<=10, PERIOD)

Expression = Sum(DAYS)