16 Replies Latest reply: Jan 7, 2015 4:29 PM by Mario Estrada

# average of top 3

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

• ###### Re: average of top 3

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.

• ###### Re: average of top 3

Hi Elena,

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

• ###### Re: average of top 3

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.

• ###### Re: average of top 3

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.

• ###### Re: average of top 3

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

• ###### Re: average of top 3

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.

• ###### Re: average of top 3

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.

• ###### Re: average of top 3

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.

• ###### Re: average of top 3

see my attached document. There is a cross table

• ###### Re: average of top 3

I'm not sure how much clearer I can make it that you can't use a cross table.  You need to user the Qlikview wizard and take it out of cross table format.

The data should look like:

Category  Code  Period   Score

A              100     1          59

A              200     1          40

A              200     2          45

B              100     1          70

Not Category       Code      Period1        Period 2       Period3         Period4

• ###### Re: average of top 3

i have changed the format of the data (no crosstab any more)

Still the formula doesn't bring a result

• ###### Re: average of top 3

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

• ###### Re: average of top 3

Hi Mario I don't understand your last formula

The average must be for all codes of this category 38.33

see attached excel

• ###### Re: average of top 3

Can you kindly attach the file ttt.xlsx ?

• ###### Re: average of top 3

enclosed

• ###### Re: average of top 3

Expression formula is

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

rangeavg (above(avg(value),0,3))

)

enclosed a solution and a straight table not showing 0 values.

Regards,