topic avg of top ten percent of values within dimension in QlikView

avg of top ten percent of values within dimension

pepe2209 — Tue, 24 Nov 2015 10:56:27 GMT

Hello I've searched a lot about retrieving the top ten percent, but this is almost always focused on limiting the table dimension to the top 10 percent records. I want something else, please look at the example (the # of records columns are just for explanatory purpose, I don't need them)

Dimenson: Quarter	#records all	avg result all	#records top 10%	avg result top 10%
Quarter 3 2015	100	15	10	21
Quarter 2 2015	110	14	11	19
Quarter 1 2015	140	15	14	22
Quarter 4 2015	100	13	10	19
Quarter 3 2015	120	16	12	22
etc

So for each "quarter" I would like to calculate the avg of the top 10% records according to the field "result" and within the dimension.

Ypur help would be much appreciated.

Regards,

Peter

Re: avg of top ten percent of values within dimension

swuehl — Tue, 24 Nov 2015 11:05:48 GMT

Maybe (would be easier to answer with a small sample QVW uploaded) something like

=Avg( TOTAL<Quarter> Aggr( If( Rank( Sum(result)) <= Count(TOTAL<Quarter>result) / 10, Sum(result) ), Quarter, resultID))

resultID is maybe just result, if you want to consider each single result record, and you don't need to group by another dimension.

as I said, these kind of questions will be a lot easier to answer with a more detailed explanation of your data model and data.

Re: avg of top ten percent of values within dimension

pepe2209 — Tue, 24 Nov 2015 11:19:21 GMT

I've added an example qvw, the data consists of only three fields: Key, quarter and result

Thanks for your help

Re: avg of top ten percent of values within dimension

swuehl — Tue, 24 Nov 2015 11:28:27 GMT

I assume you need the key here if you don't want to aggregate equal results:

=Avg( TOTAL<quarter> Aggr( If( Rank( Sum(result),4,0) <= Count(TOTAL<quarter>result) / 10, Sum(result) ), quarter, key))

Re: avg of top ten percent of values within dimension

pepe2209 — Tue, 24 Nov 2015 12:21:23 GMT

Awesome thanks!