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]

I am stamping Others with -1 here... so value shouldn't matter. I am confused? Did you try this exact expression?

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

[abhijith28]

Hi @sunny_talwar 

yeah, tried the exact expression . But i could see the "others" first.

Thanks,

[Kushal_Chawda · Accepted solution]

try blow

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

[sunny_talwar]

What is your sort order within sorting?

[sunny_talwar]

To test, I suggest converting your bar chart into a table and use my and @Kushal_Chawda 's expression to see what numbers do you get... do you get -1 for my expression for Others or not? If not, do you get null? or do you get another value.

We cannot really solve it unless you share a sample where we can see the issue. 

[abhijith28]

Hi @sunny_talwar @Kushal_Chawda 

Finally, its working fine with the exact sorting order.

Thank you for your time and providing me the solution. 

Thanks,

[abhijith28]

Hi @sunny_talwar @Kushal_Chawda 

Is it possible to calculate the max() of the measure in the text box?

Can we use    max( aggr ( count( id) , variable )) 

i tried using above logic, but its not returning any vale in the text box.

Thanks,

[Kushal_Chawda]

expression looks fine to me.. make sure that variable in aggr is the dimension on which you need to calculate max

[abhijith28]

Hi @Kushal_Chawda 

Its not returning in my text box.

Used the same expression.

Thanks,

[Kushal_Chawda]

just replace $(vDim) with State if you want to find the max value state wise