4 Replies Latest reply: May 24, 2016 5:51 AM by Carlos Daniel

# Rank top 2 of several columns

Hi there,

Let's say that i have the following data:

dimensionc1c2c3c4
dim1258
dim1359
dim0268

How can i produce a bar chart, that shows, for the dimension, the highest average columns? In this case, the chart would display two bars, the average of C3 and the average of C4.

My guess is that, for a bar chart, the dimension would be the column dimension, and i would have two calculated measures, but i'm unsure what the formula for these would be.

Thanks for the help.

• ###### Re: Rank top 2 of several columns

An option would be to use a CROSSTABLE LOAD prefix to transform your data:

CROSSTABLE (Cs, Value)

dimension,c1, c2,c3, c4

dim, 1,2, 5, 8

dim, 1,3, 5, 9

dim, 0,2, 6, 8

];

Then create a chart with dimensions dimension and Cs and a single expression

=Avg(Value)

Then limit your second dimension to show only top 2 expression values.

• ###### Re: Rank top 2 of several columns

Hi,

That works great for the example that i provided, thank you. Just a quick question, can i sort the bar graph from highest to lowest average?

Thanks.

• ###### Re: Rank top 2 of several columns

You mean, using multiple dimension values?

So you want to show top 2 Cs per dimension value, then sort the complete chart descending?

I don't think you can get this with two dimensions, but maybe with a single calculated dimension and an expression that filters the Top2.

Dimension:

=dimension & ' - ' & Cs

Expression:

=Only(Aggr(If(Rank(Avg(Value),4)<=2, Avg(Value)),dimension,Cs))

• ###### Re: Rank top 2 of several columns

Thanks. Using only Cs as a dimension also seems to work.