Qlik Community

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

1 Solution

Accepted Solutions
danielrozental
Honored Contributor II

Re: Suppress zeros and nulls in pivot table

Try adding MEASURE to the AGGR dimensions

View solution in original post

12 Replies
danielrozental
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.

LOAD * INLINE [

    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.

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

danielrozental
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.

Not applicable

Suppress zeros and nulls in pivot table

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

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?

danielrozental
Honored Contributor II

Re: Suppress zeros and nulls in pivot table

Post a sample qvw or post your expressions variables.

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

danielrozental
Honored Contributor II

Re: Suppress zeros and nulls in pivot table

Try adding MEASURE to the AGGR dimensions

View solution in original post

Not applicable

Re: Suppress zeros and nulls in pivot table

That did it, Thanks Daniel