How to display bottom 5 excluding zero.

[kavita25]

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

[shraddha_g]

I don't have access to qlikview.

But one more thing you can try is in expression editior: if(sum(Field) <>0,sum(Field))

[kavita25]

This is top 5..i want bottom 5 excluding zeroes

[antoniotiman]

I'm sorry.

Dimension :

=Aggr(If(Sum(TotalExpense) > 0,Location),Location)

Expression :

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

[Gysbert_Wassenaar · Accepted solution]

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

[kavita25]

Thank you soo much...

I didnt understand the logic you have used...Can u please explain me...

[kavita25]

Thank you for your efforts...

[kavita25]

Hi gwassenaar,

Can you please explain me the logic...

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

and why to use "  `` " this type of quotes and if i replace with single quote, the logic doesnt work.

Regards,

Kavita

[Gysbert_Wassenaar]

The part of the expression you posted already contains single quotes. The text within those single quotes must be inside those quotes. What happens if you use more single quotes is that other text strings (like =sum({<LinkDate={ ) will be inside quotes instead of the text that should be inside those quotes. To put it another way, you can't nest single quotes within other single quotes. Therefore another type of quotes needs to be used. Since I also already used double quotes in another part of the expression I had to use either the back-tick quotes or sets of square brackets. See QlikView Quoteology for more information.