Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
Hi Elena,
please find attached the excel-sheet with the expected results ( in bold)
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.
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.
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
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.
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.
IF (rank(Aggr(avg(value),Category,code,period),0,1) <=3 ,rank(Aggr(avg(value),Category,code,period),0,1))
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.