Bar Chart --> Top 5 and default dimension value in QlikSense

[abhijith28]

Hi All, @sunny_talwar 

Can anyone help me out with this scenario.

I have a Bar Chart and have to limit the values.

1. I have to show top 5 --> sum(values) among A,B,C,D,E,F exclude 'Default'

2. I have to show sum(values) for 'Default' value always in Bar Chart.

3. Others --> This includes the values which are apart from Top 5 and default.

Thanks & Regards,

Abhijith

[sunny_talwar]

May be try this as your calculated dimension

Aggr(
    If(Rank(Sum({<Dim -= {'Default'}>}values)) < 6 or Dim = 'Default', Dim, 'Other')
, Dim)

 and this as your expression

Sum({<Dim += {'Default'}>} values)

[abhijith28]

Hi @sunny_talwar 

Thanks for sharing the logic.

Now i am finding with the sorting. I need to sort with 3 different conditions.

1. Top 5 in desc order.

2. then Others.

3. last value always it should 'default'

[sunny_talwar]

Are you able to share a sample where we can see this? Or you can play around with an expression will give the max value to what you want to see as the first and lowest to default.

[abhijith28]

Hi @sunny_talwar 

So, this the dimension expression:

This is my Dimension.

=if(State='Defeased',State,
If(Aggr(Rank( (sum({<State-={'Defeased'} >} [Balance])/sum({<State-={'Defeased'} >}[Balance ($)))),State)<=5, State,'Others'))

This is the expression.

Sum(Balance)/sum(Current Bal)

I need to sort on 3 conditions:

1: Top 5 State on desc order.

2: show Other

3: Defeased

[sunny_talwar]

Can you share a sample app or some sample data where we can test this out?

[abhijith28]

Hi @sunny_talwar 

Please find the attached File.

 I have to show the  top 5 states, apart from defeased.

Thanks,

[sunny_talwar]

Is this what you want?

Dimension

=Aggr(
	If(State = 'Defeased', State,
	If(Rank((Sum({<State-={'Defeased'}>} Balance) + 0 * Sum({<State-={'Defeased'}>} Balance))/(Sum({<State-={'Defeased'}>} CurrentBal) + 0 * Sum({1<State-={'Defeased'}>} CurrentBal))) <= 5, State, 'Others'))
, State)

Expression

(Sum(Balance) + 0 * Sum(Balance))/(Sum(CurrentBal) + 0 * Sum({1} CurrentBal))

[Kushal_Chawda]

see the attached

[abhijith28]

Hi @sunny_talwar 

Thanks for sharing the solution. The output should be sorted for Top 5 states first, then others, last should be defeased.

@Kushal_Chawda 

Thanks for sharing the solution. I have a doubt. Cold you please tell me , why the last condition with -100 and -50 are used for sorting.

=If(aggr(Rank( (sum({<State-={'Defeased'} >} Balance)/sum({<State-={'Defeased'} >}CurrentBal))),State)<=5 ,
sum(Balance)/sum(CurrentBal),
if( State='Defeased',
(sum(Balance)/sum(CurrentBal))-100,
(sum(Balance)/sum(CurrentBal))-50))

Thanks,