Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

bumin
Contributor II

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

Tags (2)
16 Replies
elena888
Valued Contributor

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.

bumin
Contributor II

Re: average of top 3

Hi Elena,

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

elena888
Valued Contributor

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.

qvelhoff
Valued Contributor

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.

bumin
Contributor II

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

qvelhoff
Valued Contributor

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. 

bumin
Contributor II

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.

Not applicable

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))

qvelhoff
Valued Contributor

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.

Community Browser