Suppress zeros and nulls in pivot table

[#1]

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

[#2]

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?

[danielrozental]

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?

[#4]

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!