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

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

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