3 Replies Latest reply: Jun 18, 2018 9:45 AM by Justin Dallas RSS

    Show Zero in Table for Sum

    Justin Dallas

      Hello Everyone,

       

      I have a table where I would like every dimension to have a '0' when it doesn't meet the Set Analysis requirement.  For instance, I have this dataset.

       

      TestData:
      LOAD * Inline
      [
      'UserId', 'ProductType','Count'
          1, 'Cookies', 3
          1, 'Biscuits', 4
          2, 'Peas', 5
          2, 'Sugar', 6
      ]
      ;
      
      
      EXIT Script
      ;
      

       

       

      Then, I have a table showing the UserId and a Set Analysis value of 'SUM({<[ProductType]={'Peas'}>} Count)'.  When a UserId doesn't have any Peas, the table throws the row away, giving me this.

       

      Capture3.PNG

       

      What I would like is something like this.

       

      Capture4.PNG

       

      And of course, I've got that 'Include Zero Values' checked (but this isn't quite a zero value, it's more like some kind of Null).

       

      Capture5.PNG

       

      Any help is greatly appreciated.