5 Replies Latest reply: Jan 1, 2018 7:40 PM by jennifer Gan

# Cant get correct count at aggr level

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.

ChannelBUMonthCount(DISTINCT Qty)aggr((Count(  Distinct Qty)) ,Month)
9-
webCI127

## -

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

Try this

Count(DISTINCT TOTAL <Channel, Month> Qty)

• ###### Re: Cant get correct count at aggr level

Or this

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

• ###### Re: Cant get correct count at aggr level

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

Thank you! It definitely helps a lot!