qlikviewqliksense

Qlik Sense: Sum by Distinct id

Generate sample data:

SELECT 1 ID1, 1 ID2, 100 Amount FROM dual UNION ALL
SELECT 1 ID1, 2 ID2, 100 Amount FROM dual UNION ALL
SELECT 1 ID1, 3 ID2, 100 Amount FROM dual UNION ALL
SELECT 2 ID1, 1 ID2, 500 Amount FROM dual UNION ALL
SELECT 2 ID1, 2 ID2, 500 Amount FROM dual;

This is the current output in Qlik Sense:

ID1 ID2 Amount
Totals 1,300
1 1 100
1 2 100
1 3 100
2 1 500
2 2 500

The total amount should be based on ID1, so it should be 600 only. I tried using this formula to show the Amount by following this answer.

Sum(Aggr(Sum(DISTINCT Amount), ID1))

But the resulting output displays the other amounts as 0 like this:

ID1 ID2 Amount
Totals 600
1 1 100
1 2 0
1 3 0
2 1 500
2 2 0

Expected output:

ID1 ID2 Amount
Totals 600
1 1 100
1 2 100
1 3 100
2 1 500
2 2 500
Solution

  • If you split the data into 2 tables and associate across ID1 then Qlik will do what you want. As a table expression I have no suggestions.

    INLINE:
load * inline [
ID1,ID2,Amount
1,  1,  100
1,  2,  100
1,  3,  100
2,  1,  500
2,  2,  500];

ID1Amount:
load distinct 
  ID1,
  Amount
resident INLINE;

ID1ID2:
load distinct 
  ID1,
  ID2
resident INLINE; drop table INLINE;

    Results table