topic How to hide zero values in a pivot table chart in QlikView

How to hide zero values in a pivot table chart

johnpaul — Fri, 30 Sep 2011 23:30:40 GMT

I have been researching this for a while, but haven't found out how to do.

I want to hide values in a column that are zero.

One way to do it is to use the Visual Cues to set the color to be white when the value is zero or below. The problem with this is that if the value is negative, then it won't be visible.

Any ideas? Thanks, Jp.

How to hide zero values in a pivot table chart

johnpaul — Fri, 30 Sep 2011 23:32:57 GMT

I found a way, using a combination of an if and set analysis:

if(sum({<Billable_Flag-={'Billable'}, Company_RecID -= {$(vInternalCompanyID)}>} Hours_Actual) =0, ' ', sum({<Billable_Flag-={'Billable'}, Company_RecID -= {$(vInternalCompanyID)}>} Hours_Actual))

How to hide zero values in a pivot table chart

its_anandrjs — Sat, 01 Oct 2011 04:54:44 GMT

Hi,

You want to hide the 0.00 values and also its corresponding values in column Hours, Hrs. Bill

Let me know

Rgds

Anand

Re: How to hide zero values in a pivot table chart

its_anandrjs — Sat, 01 Oct 2011 05:01:38 GMT

Hi,

Write this code by expanding the expression by click on + sign and write code in Text Color

If( [Non Bill] = 0, White( ) )

This will hide only 0.00 values in your chart hope this helps

Let me know about this.

See sample file also

Rgds

Anand

Re: How to hide zero values in a pivot table chart

its_anandrjs — Sat, 01 Oct 2011 18:50:36 GMT

Hi John,

Hope you got correct answer from the solution that i provide to you, if it not works let me know about that.

Rgds

Anand

How to hide zero values in a pivot table chart

SunilChauhan — Sat, 01 Oct 2011 18:59:08 GMT

got to presentation-> supress zero values

if not then please share the sample

How to hide zero values in a pivot table chart

its_anandrjs — Sat, 01 Oct 2011 19:01:29 GMT

Hi,

No Question is to keep the other column value should be display if we use Supress zero values the all other column values which are dependent will not display.

Rgds

Anand

Re: How to hide zero values in a pivot table chart

johnpaul — Sun, 02 Oct 2011 00:36:53 GMT

The problem with this:

If( [Non Bill] = 0, White( ) )

method is that it will also hide the column heading.

The column heading will display OK on the sample which you posted if you have the chart type set to Straight Table, but if it is set to Pivot, it won't.

So for Pivot charts, setting the value to a space might be better.

Re: How to hide zero values in a pivot table chart

its_anandrjs — Sun, 02 Oct 2011 05:26:51 GMT

Hi,

Thanks But see the attached sample It works for both Pivot and Straight Table also.

Rgds

Anand

How to hide zero values in a pivot table chart

johnpaul — Sun, 02 Oct 2011 07:56:51 GMT

Hi Anand,

I have looked at the attached sample and the problem still persists; the column heading on the Pivot Table is also white:

Re: How to hide zero values in a pivot table chart

jonathandienst — Sun, 02 Oct 2011 09:38:56 GMT

The header line in a pivot table takes its display cues from the first line, so if the first line is set to display white, so will the header. You may be able to get around the problem using cell custom formats.

Or if you will allow the first row to display the zero, then use

If( [Non Bill] = 0 And RowNo() > 0, White( ) )

Not quite what you want, but perhaps the best possible if you don't want to use the space approach.

Hope this helps

Jonathan

Re: How to hide zero values in a pivot table chart

its_anandrjs — Sun, 02 Oct 2011 10:09:17 GMT

Hi,

Ok i got it for this you want in header the Sum value that is 4 is display in pivot table if so then put

Sum( HrsBill ) in pivot chart only because pivot chart works differently because in pivot chart the Sum value depend on dimension that you put so after putting Sum( ) function

Go to presentation tab -> Select Dimension and Expression -> that is ColA in example if you have working application do it same there -> and then check mark Show Partial Sums -> Subtotals on Top / Subtotals at Bottom then you see the Column Heading.

See the attached sample file with different variation.

HTH

Rgds

Anand

Re: How to hide zero values in a pivot table chart

Sun, 02 Oct 2011 12:51:04 GMT

Hi,

You can set the expression like below to hide "0" values.

if(Hours=0,'',Hours)

if(HrsBill=0,'',HrsBill)

if([Non Bill]=0,'',[Non Bill])

Hope you were looking for the above expression.

Thanks,

Sajeevan

Re: How to hide zero values in a pivot table chart

Mon, 18 Jun 2012 03:24:27 GMT

thanks you

Re: How to hide zero values in a pivot table chart

Mon, 18 Jun 2012 03:25:45 GMT

Thanks a lot, it can solve !!