0 Replies Latest reply: Oct 19, 2016 9:11 PM by Eryk Budzynski RSS

    Measures in a table summing over all field values except the value in the current row

    Eryk Budzynski

      Hi All

       

      I'm trying to construct a table in Qlik Sense as per below and am not sure how I can achieve the derived measures using set analysis etc.

       

      Basically there is a table with 2 dimensions (Field1 and Field2) and a measure Sum(Measure1). I need to add 4 additional measures (A, B, C, D) which will then be used for additional calculations, but for now, I'm just trying to work out how to calculate A, B, C and D.

       

      A: Sum(Measure1) in total dataset with Field1 from the row and Field2 from the row - this one's straight forward enough.

      B: Sum(Measure1) in total dataset with Field1 from the row and all Field2s (excluding Field2 from the row)

      C: Sum(Measure1) in total dataset with all Field1s (excluding Field1 from the row) and Field2 from the row

      D: Sum(Measure1) in total dataset with all Field1s (excluding Field1 from the row) and all Field2s (excluding Field2 from the row)
      A+B+C+D will always equal the sum of Measure1 across the entire dataset.
      As an example:
      Field1          Field2          Sum(Measure1)          A          B          C          D
      -----------------------------------------------------------------------------------------------------------
      D1               R1               3                                   3          4          5          6
      D1               R2               4                                   4          3          6          5
      D2               R1               5                                   5          6          3          4
      D2               R2               6                                   6          5          4          3
      Any help would be greatly appreciated.
      Thanks
      Ryk