# QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Highlighted
Not applicable

## Suppress zeros and nulls in pivot table

Hi,

I built a pivot table (image included) that includes several calculations that naturally have zero and/or null values at specific levels.  However, even with suppress zero and suppress null selected, they still show up.. is there something else that I am missing?

Thanks,

Joe

Tags (2)
1 Solution

Accepted Solutions
Highlighted
Honored Contributor II

## Re: Suppress zeros and nulls in pivot table

Try adding MEASURE to the AGGR dimensions

12 Replies
Honored Contributor II

## Suppress zeros and nulls in pivot table

Problem is that every value is a different expression, you won't be able to hide some expressions for a particular value in the dimension.

What can you do? You could try turning your different expressions into an isolated dimension.

DIMAUX

Monthly Volume

Days in Month

Avg Daily Census

Paid FTEs

];

Then you have DIMAUX as a dimension and a single expression

if(DIMAUX = 'Monthly Volume', sum(something...), 0)

+

if(DIMAUX = 'Days in Month', sum(something else...), 0)

+

if(DIMAUX = 'Avg Daily Census', sum(another thing...), 0)

... etc.

Highlighted
Not applicable

## Suppress zeros and nulls in pivot table

Hi Daniel,

Thanks for the reply.  One question. o I simply add the "if(DIMAUX = 'Monthly Volume', Monthly volume calc) to my Monthly Volume calc?  If not, where do I put this code at?

Thanks,

Joe

Highlighted
Honored Contributor II

## Re: Suppress zeros and nulls in pivot table

You should remove all the expressions you currently have and add that code to your new single expression.

Highlighted
Not applicable

## Suppress zeros and nulls in pivot table

Got it.  Let me give it a try.  Thanks for the help!

Highlighted
Not applicable

## Re: Suppress zeros and nulls in pivot table

When I try the following code:

if

(MEASURE='Monthly Volume', \$(varMonthlyVolume) ,0)
+
if(MEASURE='Days in Month',\$(varDaysInMonth) ,0)

I only get one line and that is for Monthly volume.  I substituted a '1' for the the \$(varDaysinMonth) and it will return the 1 and the Days in Month Row.  I removed the variable reference and put in the actual calculation and that didnt work either.

What am I missing?

Highlighted
Honored Contributor II

## Re: Suppress zeros and nulls in pivot table

Post a sample qvw or post your expressions variables.

Highlighted
Not applicable

## Re: Suppress zeros and nulls in pivot table

varMonthlyVolume =

Sum( aggr(distinct sum({1<MEASURE_KEY={1}>} Value), SCENARIO_DESC,MONTH,DEPT_NAME) ) *(1+ VarCensusToggle )

The VarCensusToggle is a slider bar that allows the user to change the monthly volume

varDaysInMonth =

sum

( aggr(distinct sum({1<MEASURE_KEY={2}>} Value),SCENARIO_DESC, MONTH,YEAR,DEPT_NAME) )

Highlighted
Honored Contributor II

## Re: Suppress zeros and nulls in pivot table

Try adding MEASURE to the AGGR dimensions

Highlighted
Not applicable

## Re: Suppress zeros and nulls in pivot table

That did it, Thanks Daniel