Solved: How to display bottom 5 excluding zero. - Qlik Community

kavita25 · ‎2016-10-14

Hi,

I want to show bottom 5 in the bar chart excluding zero values.

My expression is : Total expenses/total budget *100

I found this Showing Top 5 and Bottom 5 on Bar Chart which shows bottom and top excluding zero.

But i am little bit confuse while using my logic with the given logic in the link.

Please help me with this issue..

Its urgent...

Regards,

Kavita

Gysbert_Wassenaar · ‎2016-10-17

Try this expression:

(sum({<Location={"=rank(-

(sum({<Location={`=sum({<LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)>0`},LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)/

sum({<Location={`=sum({<LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)>0`},LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}[Op.Bal]))

)<6"},LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)/

sum({<Location={"=rank(-

(sum({<Location={`=sum({<LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)>0`},LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)/

sum({<Location={`=sum({<LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)>0`},LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}[Op.Bal]))

)<6"},LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}[Op.Bal]))

talk is cheap, supply exceeds demand

View solution in original post

kavita25 · ‎2016-10-17

gwassenaar‌

swuehl

mayilvahanan

stalwar1‌

Please help me with this issue...

MayilVahanan · ‎2016-10-17

HI

Can you post a sample data?

Thanks & Regards, Mayil Vahanan R
Please close the thread by marking correct answer & give likes if you like the post.

shraddha_g · ‎2016-10-17

uncheck Show Zero values at expression tab.

It might help.

kavita25 · ‎2016-10-17

Yes...

PFA !

Anonymous · ‎2016-10-17

Hi,

Please check this link:

TOP 10 and Bottom 10 Customer by Revenue Amount

kavita25 · ‎2016-10-17

There's no such option in expression tab

shraddha_g · ‎2016-10-17

Check at Presentation Tab, Suppress Zero-Values option

kavita25 · ‎2016-10-17

Can u pls check the sample data...

I already checkd those options

antoniotiman · ‎2016-10-17

Hi,

may be

If(Rank((sum({<LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)/
sum({<LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}[Op.Bal]))) <= 5,
(sum({<LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}TotalExpense)/
sum({<LinkDate={'>=$(vCalMinDate)<=$(vCalCurrentDate)'},Txn_Type={'RnR'}>}[Op.Bal])))