topic Re: Cant get correct count at aggr level in App Development

Cant get correct count at aggr level

jengancc — Wed, 22 Dec 2021 15:42:54 GMT

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

Re: Cant get correct count at aggr level

sunny_talwar — Wed, 27 Dec 2017 16:30:38 GMT

Try this

Count(DISTINCT TOTAL <Channel, Month> Qty)

Re: Cant get correct count at aggr level

sunny_talwar — Wed, 27 Dec 2017 16:31:30 GMT

Or this

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

Re: Cant get correct count at aggr level

jengancc — Thu, 28 Dec 2017 00:51:08 GMT

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.

Re: Cant get correct count at aggr level

sunny_talwar — Thu, 28 Dec 2017 08:46:30 GMT

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

Re: Cant get correct count at aggr level

jengancc — Tue, 02 Jan 2018 00:40:48 GMT

Thank you! It definitely helps a lot!

topic Re: Cant get correct count at aggr level in App Development

Cant get correct count at aggr level

web

DB

1

5

-

web

CI

2

3

-

Re: Cant get correct count at aggr level

Re: Cant get correct count at aggr level

Re: Cant get correct count at aggr level

Re: Cant get correct count at aggr level

Re: Cant get correct count at aggr level