Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I would like to ask a question with regards to creating top and bottom 5.
I have 3 fields
1- average yearly miles by 2- Device names
I have 10 customers
I kept the customer name in filters
I want to know top 5 and bottom 5 average yearly miles by devices for each customer when I select customer in filters.
if anyone can help with this.
thanks in advance
Sort of depends how you want to display the top / bottom 5. Let's say you have a table with dimensions Customer and Device name, and you want to show the ranks (by average yearly miles) but only for top or bottom 5. You can have above 2 dimensions and an expression something like this:
if(rank(avg(miles), 1, 1) <=5 or rank(-avg(miles), 1, 1) <=5, avg(miles))
if you wanted to show list of devices in a text box for a selected customer you could do this:
=concat(aggr(if(rank(avg(miles), 1, 1) <=5 or rank(-avg(miles), 1, 1) <=5, avg(miles)), devices ), devices & ', ')
Hi.. Thanks for the reply. I want to display top 5 and bottom 5 devices by average yearly miles..
I have 10 customers who use same devices so if I change the customer name in the filter the devices should change accordingly to top 5 and bottom 5 devices for each customer..
thank you
So that should work. You can have a table for "Top 5"
Dimension: Device
Measure: (label = Rank) if(rank(avg(miles), 1, 1) <=5 , rank(avg(miles), 1, 1) )
and a table for "Bottom 5"
Dimension: Device
Measure: (label = Rank) if(rank(-avg(miles), 1, 1) <=5 , rank(avg(miles), 1, 1) )