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

[Kushal_Chawda]

@abhijith28  - Because we have selected sorting as Descending.  So for Top 5 actual expression will work fine, but for other and Defeased to sort is as per requirement we need to do a little trick. Hence,  to sort others  after top 5 I did subtracted some amount and same for the Defeased

[abhijith28]

Hi @Kushal_Chawda 

For this data sets it works fine, but i have a data set with Millions for Balance.

If i hard code the sorting with -100 & -50, it doesn't return the expected output.

Can we have any other expression to achieve this scenario. So, that the sorting would be dynamic

Thanks,

[Kushal_Chawda]

see the attached

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

[sunny_talwar]

Here is a sorting expression you can use

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

[sunny_talwar]

Static numbers should be a better option for Others and Defeased because else it will have to do that calculation for those two pieces which might be slightly slower. 

[Kushal_Chawda]

Somehow static values were not working as expected in my app. Sometime it gives strange behavior.

[sunny_talwar]

Was this with selection or without selection?

[abhijith28]

Hi @sunny_talwar 

Implemented the same sorting expression of yours. But i could see the "others" as my first bar.

Because "others" contains the highest value. 

Thanks,

[Kushal_Chawda]

@abhijith28  did you try what I suggested?

[abhijith28]

@Kushal_Chawda 

Awesome! Really helpful.

all sceanrios its working fine. But in some scenario where defeased value is "0".

Then this rules breaks, "defeased" comes first then "others".

Thanks,