Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
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
Master II
Master II

Try adding MEASURE to the AGGR dimensions

View solution in original post

12 Replies
danielrozental
Master II
Master II

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
Author

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
Master II
Master II

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

Not applicable
Author

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

Not applicable
Author

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
Master II
Master II

Post a sample qvw or post your expressions variables.

Not applicable
Author

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
Master II
Master II

Try adding MEASURE to the AGGR dimensions

Not applicable
Author

That did it, Thanks Daniel