Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

qdatainformatic
New Contributor II

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.

1 Solution

Accepted Solutions
MVP
MVP

Re: Rank top 2 of several columns

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.

4 Replies
MVP
MVP

Re: Rank top 2 of several columns

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.

qdatainformatic
New Contributor II

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.

MVP
MVP

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

qdatainformatic
New Contributor II

Re: Rank top 2 of several columns

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