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

Thu, 20 Oct 2016 01:11:44 GMT

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

😧 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

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

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