topic Re: How to display bottom 5 excluding zero. in QlikView

How to display bottom 5 excluding zero.

kavita25 — Fri, 14 Oct 2016 13:24:52 GMT

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

Re: How to display bottom 5 excluding zero.

kavita25 — Mon, 17 Oct 2016 06:24:52 GMT

gwassenaar‌

swuehl

Re: How to display bottom 5 excluding zero.

MayilVahanan — Mon, 17 Oct 2016 07:09:13 GMT

Can you post a sample data?

Re: How to display bottom 5 excluding zero.

shraddha_g — Mon, 17 Oct 2016 07:19:54 GMT

uncheck Show Zero values at expression tab.

It might help.

Re: How to display bottom 5 excluding zero.

kavita25 — Mon, 17 Oct 2016 07:24:54 GMT

Yes...

PFA !

Re: How to display bottom 5 excluding zero.

Anonymous — Mon, 17 Oct 2016 07:27:19 GMT

Hi,

Please check this link:

TOP 10 and Bottom 10 Customer by Revenue Amount

Re: How to display bottom 5 excluding zero.

kavita25 — Mon, 17 Oct 2016 07:30:02 GMT

There's no such option in expression tab

Re: How to display bottom 5 excluding zero.

shraddha_g — Mon, 17 Oct 2016 07:33:17 GMT

Check at Presentation Tab, Suppress Zero-Values option

Re: How to display bottom 5 excluding zero.

kavita25 — Mon, 17 Oct 2016 07:42:36 GMT

Can u pls check the sample data...

I already checkd those options

Re: How to display bottom 5 excluding zero.

antoniotiman — Mon, 17 Oct 2016 07:47:36 GMT

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

Re: How to display bottom 5 excluding zero.

shraddha_g — Mon, 17 Oct 2016 07:50:21 GMT

I don't have access to qlikview.

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

Re: How to display bottom 5 excluding zero.

kavita25 — Mon, 17 Oct 2016 08:39:49 GMT

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

Re: How to display bottom 5 excluding zero.

antoniotiman — Mon, 17 Oct 2016 09:06:51 GMT

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

Re: How to display bottom 5 excluding zero.

Gysbert_Wassenaar — Mon, 17 Oct 2016 09:21:32 GMT

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

Re: How to display bottom 5 excluding zero.

kavita25 — Mon, 17 Oct 2016 09:41:34 GMT

Thank you soo much...

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

Re: How to display bottom 5 excluding zero.

kavita25 — Mon, 17 Oct 2016 09:41:55 GMT

Thank you for your efforts...

Re: How to display bottom 5 excluding zero.

kavita25 — Wed, 19 Oct 2016 10:44:25 GMT

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

Re: How to display bottom 5 excluding zero.

Gysbert_Wassenaar — Wed, 19 Oct 2016 10:53:19 GMT

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.

topic Re: How to display bottom 5 excluding zero. in QlikView

How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

mayilvahanan

stalwar1‌

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.

Re: How to display bottom 5 excluding zero.