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

    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
          Daniel Rozental

          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.

          • 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
                    Daniel Rozental

                    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!