12 Replies Latest reply: Jul 22, 2011 4:50 PM by jukes231

# 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

• ###### 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.

• ###### 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

• ###### 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.

• ###### Suppress zeros and nulls in pivot table

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

• ###### 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?

• ###### Re: Suppress zeros and nulls in pivot table

Post a sample qvw or post your expressions variables.

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

• ###### Re: Suppress zeros and nulls in pivot table

Try adding MEASURE to the AGGR dimensions

• ###### Re: Suppress zeros and nulls in pivot table

That did it, Thanks Daniel

• ###### Re: Suppress zeros and nulls in pivot table

I do have a follow-up question.  I am in the process of converting my expressions and ran into a issue that I dont understand.  When I put the following code (see below) into a variable and reference it in an expression, it shows up.  However, if I put it in the code block as discussed above, it errors out.  I did make sure I put the Measure dimension into the Aggr.

However, if I embed the calculation directly into the If script (as described above) it works.

Here is the variable expression:

=

avg(
aggr(
DISTINCT
(
(
avg( aggr(distinct sum({1<MEASURE_KEY={1}>} Value),MEASURE, FORECAST_LEVEL_DESC, SCENARIO_DESC, MONTH,DEPT_NAME) ) /
avg( aggr(distinct sum({1<MEASURE_KEY={2}>} Value),MEASURE, FORECAST_LEVEL_DESC, SCENARIO_DESC, MONTH,DEPT_NAME) )
*
avg( aggr(distinct sum({1<MEASURE_KEY={9}>} Value),MEASURE, SCENARIO_DESC, MONTH,DEPT_NAME) )    *
sum( aggr(distinct sum({1<MEASURE_KEY={2}>} Value),MEASURE, SCENARIO_DESC, MONTH,YEAR,DEPT_NAME) )
) /
(2080/365*
sum( aggr(distinct sum({1<MEASURE_KEY={2}>} Value),MEASURE,SCENARIO_DESC, MONTH,YEAR,DEPT_NAME) )
)
),
MEASURE,FORECAST_LEVEL_DESC,SCENARIO_DESC, MONTH,DEPT_NAME
)
)

This calculation is referenced alot, so would rather not have to put this large chunk in every time.

Any thoughts?

• ###### Re: Suppress zeros and nulls in pivot table

Does the expression fail or the syntax checker complains about it?

Your expression seems overly complex, why would you need to do an aggr over an aggr?

• ###### Re: Suppress zeros and nulls in pivot table
Its seems to pass the expression checker fine, it just yields a null value
this calculation is made up of several pieces.  These pieces are created in variables.  I do have it simplified as such:
avg
(
aggr(
DISTINCT
(
(
\$(varAvgDailyCensus) * \$(varPaidHourPerStat) *\$(varDaysInMonth)) /
(2080/365*
\$(varDaysInMonth))
),
MEASURE, FORECAST_LEVEL_DESC, MONTH,DEPT_NAME
)
)
If I embed this calculation (wth the variables) it returns the correct value.  If I embed this into a variable (varPaidFTEs), It yields a null value.  However, if I create another expression and reference it, it brings back the same value for all columns.  When I saw this, I thought it was due to the variables it was referencing (varAvgDailyCensus, varPaidHourPerstat, etc), so I embedded those calcs into varPaidFTEs.
Still trying to grasp the logic engine in Qlikview!