Aggr with missing groups

[markperrone]

Below is our mocked up data.

We have this expression

SUM(AGGR(

  MIN(AGGR(COUNT(DISTINCT D),A,B,C))

  ,B,C

))

Some groups is only getting one row and the min is taking that row when we need it to be a zero.

How do we default to zero if there is only one row ?

A	B	C	D
1	x	4	1
1	x	4	2
2	x	4	3
2	x	4	4
2	x	4	5
2	x	4	6
2	x	4	7
2	x	4	8
2	x	4	9
2	x	4	10
1	x	5	11
1	x	5	12
2	x	5	13
1	x	6	14
2	x	6	15
2	x	6	16
2	x	6	17
2	x	6	18
2	x	6	19
2	x	6	20
2	x	6	21
2	x	7	22

[Scotchy]

Hi

Try this out ...

So In Qlik Sense, the AGGR() function is used to create a virtual table of data, calculated on the fly, based on the dimensions and expressions you provide. Your current expression is complex as it uses nested aggregations to compute the minimum count of distinct values of D for each combination of A, B, and C, and then sums these minimum values for each combination of B and C.

However, you're facing a challenge with groups that have only one row. In such cases, the MIN() function is returning the count of that single row, which is not zero as you would expect. To address this, you need to modify the expression to handle cases where there is only one row in a group.

A solution is to use a conditional statement within the inner AGGR() function. This statement would check the count of rows in each group and return 0 if there's only one row, otherwise, it would return the count of distinct values of D. Here's how you can modify the expression:

SUM(AGGR( MIN(IF(COUNT(DISTINCT D) = 1, 0, AGGR(COUNT(DISTINCT D), A, B, C))) , B, C))

In this modified version, the IF() statement inside the MIN() function checks if the count of distinct D values is 1. If true, it returns 0. Otherwise, it proceeds with the inner AGGR() function to count distinct D values per A, B, and C. This way, you ensure that groups with only one row contribute 0 to the final sum.

[markperrone]

Thanks.  That looks like it may work.   Let me try it out.