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
