1 Reply Latest reply: May 28, 2016 6:45 AM by Stefan Wühl RSS

    Nulls not being suppressed

    Laura Castagna

      Hi,


      I've used a Pivot table to present my data.  The following is in the dimension:-

      =If(WildMatch(Code, '*CALLBUS*'), Dual('Calls-BusHelp',3),
        If(WildMatch(Code, '*CALLHR*'),DUAL( 'Calls-HR',2),
        If(WildMatch(
      ,Code '*CALLS*'), DUAL('Calls',1),' ' )))

      CALLHR & CALLS are summed from the same field.  CALLBUS is summed from a different field ( =Sum(Quantity)). I don't want to show =Sum(Quantity) under Calls or Calls-HR.

      I've tried if(Quantity <>'',sum(Quantity),'') so that results returns NULL rather than zero.  And then making sure the suppress NULLs in both presentation and under dimension.  but so far nothing is working:-


       

       

       

       

       

       

       

       

       

       

      Calls

       

       

      Calls-HR

       

       

      Calls-BusHelp

       

       

      Account

      =SUM(Purchased)

      =SUM(Remaining)

      =SUM(Quantity)

      =SUM(Purchased)

      =SUM(Remaining)

      =SUM(Quantity)

      =SUM(Purchased)

      =SUM(Remaining)

      =SUM(Quantity)

      Dolly's Flowers

      1

      1

      -

      10

      7

      -

      0

      0

      2

      Justins Land

      5

      4

      -

      0

      0

      -

      0

      0

      1

       

       

       

       

       

       

       

       

       

       

       

        • Re: Nulls not being suppressed
          Stefan Wühl

          if(Quantity <>'',sum(Quantity),'')


          is not returning NULL (use NULL() function for that), but an empty value (which is different).


          But you problem is different: You can't just hide an expression for some dimensional values, but show it for others in a Qlik pivot table chart. You can only show / hide / supress expression column for the entire chart (and that's not what you want).


          But you might be able to work around, using another dimension that basically is replacing your three expressions, and a single expression that tests the 'expression surrogate' dimension value.


          So this dimension would link three values 'Purchased', 'Remaining', 'Quantity' to your Calls-BusHelp dimension value, and only 'Purchased' and 'Remaining' to the other two.


          To make the linking easier, I would suggest to create another field in your data model replacing the current calculated dimension:


          LOAD

               Code,

               If(WildMatch(Code, '*CALLBUS*'), Dual('Calls-BusHelp',3),
            If(WildMatch(Code, '*CALLHR*'),DUAL( 'Calls-HR',2),
            If(WildMatch(
          ,Code '*CALLS*'), DUAL('Calls',1),' ' ))) AS ChartDim,

               ...

          FROM ...;


          Then you could create a link table for your expression surrogate dimension:


          LOAD * INLINE [

          ChartDim, ChartExpressionDim

          Calls_BusHelp, Purchased

          Calls_BusHelp, Remaining

          Calls_BusHelp, Quantity

          Calls-HR,Purchased

          Calls-HR,Remaining

          Calls,Purchased

          Calls,Remaining

          ];

              

          Now create your pivot table chart replacing your calculated dimension with ChartDim and adding dimension ChartExpressionDim.


          Then remove all three expressions and use a single expression:


          =Pick(Match(ChartExpressionDim,'Purchased','Remaining','Quantity'),

          Sum(Purchased),

          Sum(Remaining),

          Sum(Quantity)

          )