average of top 3

[bumin]

Hi,

I have a table with 3 dimensions

- category

- code

- period

and a value

- value

how can I get per each code and period the average of the top 3 from the  category

I have attached the qvw with the data

thanks for any help

[Anonymous]

Hi,

can you write an example of the expected result?

Every code is associated to one and only one category... I don't understand how to calculate the average.

Thanks.

[bumin]

Hi Elena,

please find attached the excel-sheet with the expected results ( in bold)

[Anonymous]

Dear Bumin,

thanks.

I found a solution for a straight table: see the attachment.

I don't know if it's a good visualization for you... but expressions should be ok.

[NickHoff]

First you'll want to convert your table with a cross table to make the data usable with Qlikview.

=aggr(if(rank(avg(scorefor user))<=3,category,code),category)  Will get you the average of the top 3 per category and code.  Once you use the cross table wizard and convert the application you'll have a column for period and a score for user column.

[bumin]

Hi Nick,

what do you mean with the string "scorefor user"?

The formula "=aggr(if(rank(avg(value))<=3,category,code),category) " gets no result

Field "value" has got the fugures to calculate the average.

in my example there is crosstable

thanks

Bumin

[NickHoff]

When you convert your crosstable using the wizard in Qlikview to a format that is workable for this solution it will work.  score for user was the field name you create from the cross table.  See this link here: QlikView Examples: Using the Crosstable Feature in QlikView

It won't work in category, code, period1, period2, period3, ect..  After you have your data in category, code, period, score format it will work. 

[bumin]

but I have used the wizard in Qlikview to convert it to a crosstable see my example file

what is "score"? Do you mean the ruslt of the crosstable? The field is called "value" in my example

Sorry but I still not understand what you mean.

[#9]

IF (rank(Aggr(avg(value),Category,code,period),0,1) <=3 ,rank(Aggr(avg(value),Category,code,period),0,1))

[NickHoff]

I mean taking the cross table out of cross table format.  Your application won't work the way you intend without having it in the correct format.  The cross table wizard in Qlikview primary use is to take your table out of cross table format.