Cant get correct count at aggr level

[jengancc]

Hi

This is my data set

Channel	BU	Month	Qty
web	CI	1	1
web	CI	1	2
web	DB	1	3
web	DB	1	4
web	DB	1	5
web	DB	1	6
web	DB	1	9
web	DB	2	4
web	DB	2	2
web	DB	2	1
web	CI	2	9
web	CI	2	8
web	CI	2	7

I wanted to get the last col to be a distinct count for the month. which would be 7 for JAN and 6 for FEB.

But you can see from the results I only get 7 for the 1st row, and 6 for the last row.

Not understanding why it is blank for the 2 middle rows.

Can you help to explain what I should do? and why is my logic wrong? Appreciate your inputs.

Channel	BU	Month	Count(DISTINCT Qty)	aggr((Count(  Distinct Qty)) ,Month)
			9	-
web	CI	1	2	7
web	DB	1	5	-
web	CI	2	3	-
web	DB	2	3	6

The effect I wanted is

Channel	BU	Month	Count(DISTINCT Qty)	aggr((Count(  Distinct Qty)) ,Month)
			9	-
web	CI	1	2	7
web	DB	1	5	7
web	CI	2	3	6
web	DB	2	3	6

[sunny_talwar · Accepted solution]

Try this

Count(DISTINCT TOTAL <Channel, Month> Qty)

[sunny_talwar]

Or this

Aggr(NODISTINCT Count(DISTINCT Qty), Channel, Month)

[jengancc]

This is great! Thanks Both works.

I understand Count(DISTINCT TOTAL <Channel, Month> Qty).

Can you explain why Aggr(NODISTINCT Count(DISTINCT Qty), Channel, Month) has to use NODISTINCT?

I am counting the number of rows. So I am wondering why NODISTINCT works but not DISTINCT.

Thanks.

[sunny_talwar]

Before I try to explain this myself, see if one of these two threads answer your question

What NODISTINCT parameter does in AGGR function?

Qlik Tips: No nodistinct

[jengancc]

Thank you! It definitely helps a lot!