Qlik Sense: Sum by Distinct id

Question

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

score 1 · Accepted Answer · answered Jun 17 '23 at 05:29

1

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;

answered Jun 17 '23 at 05:29

The Budac

1,571
1
8
10

1

Problem solved and I didn't even need to implement a complicated formula. Thank you, great answer. – rhemmuuu Jun 17 '23 at 05:45

score 1 · Answer 2 · answered Jun 19 '23 at 16:44

If you want to use a chart expression in the future, you could use this:

=Sum(Distinct Total <ID1> Amount)

Here's the explanation of the Total keyword from this Qlik Help page (see the first row in the table on that page):

Using the total qualifier inside your aggregation function disregards the dimensional value.

The aggregation will be performed on all possible field values.

The TOTAL qualifier may be followed by a list of one or more field names within angle brackets. These field names should be a subset of the chart dimension variables. In this case, the calculation is made disregarding all chart dimension variables except those listed, that is, one value is returned for each combination of field values in the listed dimension fields. Also, fields that are not currently a dimension in a chart may be included in the list. This may be useful in the case of group dimensions, where the dimension fields are not fixed. Listing all of the variables in the group causes the function to work when the drill-down level changes.

I tried using this and it also works, the formula is not even that complicated. I thought `Sum` and `Total` are the same, turns out they are not. Thank you for this answer. — rhemmuuu, Jun 20 '23 at 01:17

Qlik Sense: Sum by Distinct id

2 Answers2