topic Re: Exclude Zero bars in QlikView

Exclude Zero bars

New-Qlik — Thu, 25 May 2017 16:21:16 GMT

HI ,

I have a requirement to exclude zero bar form a bar chart.

I have two dim and one exprsssion bar chart.

For dim who has no entry wrt to count are also coming in bar chart just name on axis.

how can I exclude this.

I have tried all supress zero, null etc nothing working

Re: Exclude Zero bars

Anil_Babu_Samineni — Thu, 25 May 2017 16:25:57 GMT

Can you please show the image, Please do supress Dimension level and Presentation respect

Re: Exclude Zero bars

kamal_sanguri — Fri, 26 May 2017 07:36:15 GMT

The issue may be because of two dimensions, even if there is no Expression value for one dim other dim may have.

Pls check the attached doc. where you will see that despite the fact that there is no value for Expression, we have place holder for bar( blank space) in bar chart.

Re: Exclude Zero bars

New-Qlik — Sat, 27 May 2017 06:47:07 GMT

!Thanks Kumar,

but I want both dim in x axis and legend should be of expression .

Please see attached your application with changes . in this for 2001 I am getting zero bars for march n april month .

I want to exclude that

Re: Exclude Zero bars

qlikview979 — Sat, 27 May 2017 07:54:05 GMT

Hi,

it will give Zero for 2001 ,March,April no data in your source.

Re: Exclude Zero bars

New-Qlik — Sat, 27 May 2017 08:09:35 GMT

thanks rite so I don't want that in axis , just jan feb should come .for 2001

how to restrict that

Re: Exclude Zero bars

Anil_Babu_Samineni — Sat, 27 May 2017 08:39:41 GMT

There is no default option to deserve this issue? Hope mrkachhiaimp‌ offer you work around. Let's wait for his response

Re: Exclude Zero bars

New-Qlik — Sat, 27 May 2017 10:03:36 GMT

okay. @manish kindly guide

Re: Exclude Zero bars

MK_QSL — Sat, 27 May 2017 10:32:46 GMT

Like this?

Please note that there is no direct approach to handle in QlikView Bar Chart while using multiple dimension.

Thanks to rubenmarin‌ for suggesting this long time ago.

Re: Exclude Zero bars

New-Qlik — Sat, 27 May 2017 10:39:11 GMT

Thanks Manish,I am not able to see the query you have posted sometime back inorder to get this result .

Can u please post again and if u can explain little bit .

Thanks in advance

Re: Exclude Zero bars

MK_QSL — Sat, 27 May 2017 10:41:01 GMT

Script

Data:

LOAD

Dim1,

Dim2,

Sales,

Dual(Dim1 & chr(10) & Dim2, autonumber(Dim1) & Ord(Dim2)) as TempDim1

Inline

[

Dim1, Dim2, Sales

2001,Jan,100

2001,Feb,150

2002,Jan,200

2002,Mar,100

2003,Jan,250

2003,Apr,400

2004,May,150

2004,Jun,350

];

Concatenate

LOAD Distinct

Dim1,

'Z1' as Dim2,

Dual('', AutoNumber(Dim1) & Ord('Z')) as TempDim1,

'0' as Sales

Resident Data;

Bar Chart

Dimension

=Aggr(TempDim1, Dim1, Dim2)

Tick Suppress When Value is Null

Expression

SUM(Sales)

Re: Exclude Zero bars

Anil_Babu_Samineni — Sat, 27 May 2017 11:01:37 GMT

Manish Bhai, Why do we need Concatenate Table? Without Table we can achieve, right?

Re: Exclude Zero bars

New-Qlik — Sat, 27 May 2017 11:05:34 GMT

ok I missed something

1. I have three dimen

2. my expression is count(status)

now if I assign '0' as Status it is coming in legend also like status closed, opem and 0

I can exclude it but now sure howz this behaving.

Manish can u share your app the test one only.

Re: Exclude Zero bars

Anil_Babu_Samineni — Sat, 27 May 2017 11:10:13 GMT

You can use same Manish recommend

Re: Exclude Zero bars

MK_QSL — Sat, 27 May 2017 11:10:42 GMT

loveisfail

Concatenate is better option as we shouldn't keep separate table only for few lines. Performance would be better with concatenating them.

avneet85

Better you provide your application, along with the output you needed in excel file. We will work on it.

Re: Exclude Zero bars

MK_QSL — Sat, 27 May 2017 11:20:16 GMT

Not a good idea to use three dimensions on x axis. As a rule, when you have 3 dimensions, you should show chart as two dimensions on x axis with stacked chart based on 3rd dimension values and 3 rd dimension as legend.

Re: Exclude Zero bars

New-Qlik — Sat, 27 May 2017 11:40:26 GMT

Can u please share Qvw for same

Re: Exclude Zero bars

New-Qlik — Sat, 27 May 2017 11:41:10 GMT

ok let me create one same app as replica , will share soon

Re: Exclude Zero bars

kamal_sanguri — Mon, 29 May 2017 08:17:05 GMT

I understand your concern although mrkachhiaimp‌ has given a possible solution. Still you can think of changing a chart type.

Use of Block chart instead of Bar will solve your problem.

Re: Exclude Zero bars

New-Qlik — Mon, 29 May 2017 09:42:09 GMT

Hi Please find attached sample application