topic Rank top 2 of several columns in App Development

Rank top 2 of several columns

Anonymous — Mon, 23 May 2016 14:01:23 GMT

Hi there,

Let's say that i have the following data:

dimension	c1	c2	c3	c4
dim	1	2	5	8
dim	1	3	5	9
dim	0	2	6	8

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

swuehl — Mon, 23 May 2016 17:54:55 GMT

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

CROSSTABLE (Cs, Value)

LOAD * INLINE [

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

Anonymous — Mon, 23 May 2016 18:24:21 GMT

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

swuehl — Mon, 23 May 2016 18:55:10 GMT

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

Anonymous — Tue, 24 May 2016 09:51:16 GMT

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