6 Replies Latest reply on Oct 18, 2016 6:30 AM by xinyi.ng

# Counting distinct by category

Hi

I have this following set of data:

 Key Category Check_Code 1 A 1 1 B 2 2 C 1 2 C 1 3 A 1 3 B 2 3 C 3

My aim is to create a bar chart to list the count of distinct key, by category.

The complication comes in because for every unique key with different categories, it shld fall into just 1 category. Do determine which category it falls under (based on the lowest check_code for every unique key). So I should get something like this:

 Key Category Check_Code Final Category 1 A 1 A 1 B 2 A 2 C 1 C 2 C 1 C 3 A 1 A 3 B 2 A 3 C 3 A

Eventually I hope to get a bar chart with this breakdown:

 Category Count(Distinct(Key)) A 1 B 0 C 1

Appreciate if someone could assist please, thanks!

• ###### 1. Re: Counting distinct by category

Hi Xinyi,

So one way that might work for you is as follows....

First we want to only take the 1st value from each Category/Key combination. We can do this with an LoD

[Final Category]

{FIXED [Key]: MIN([Category])}

This gives you the 2nd table...which you can see on the "working" tab

Next we want to only COUNT a Key if it's Category and Final Category match (else NULL, meaning it won't get counted in a COUNTD)

[Key to Count]

IIF([Category]=[Final Category],[Key],NULL)

and then we can bring in Category and COUNTD([Key to Count]) to get your final result. Although I make it A = 2, B = 0, and C = 1 (there are 2 distinct Keys for Final Category = A)

Hope that helps and makes sense.

• ###### 2. Re: Counting distinct by category

Hi Simon

It worked! Thank you so much.

I have a question, what does minimum(dimension var.) actually result in? The first record or the first record sorted based on alphabetical order?

Regards

Xin Yi

• ###### 3. Re: Counting distinct by category

`...what does minimum(dimension var.) actually result in? The first record or the first record sorted based on alphabetical order?`

...if this field were numbers it would be the smallest number, but for strings, it is the first in alphabetical order (equally MAX would bring back the last), which is why the A is returned (it's just coincidence it's also the first!).

• ###### 4. Re: Counting distinct by category

Hi Simon

Understood!

Assuming I want to output the final category = category based on the minimum check code, how shld I modify the derivation of the final category?

[Final Category]

{FIXED [Key]: MIN([Category])}

 Key Category Check_Code Final Category 1 A 1 A 1 B 2 A 2 C 1 C 2 A 2 C 3 Z 1 Z 3 B 2 Z 3 C 3 Z
• ###### 5. Re: Counting distinct by category

So assuming you always had a [Code Check] = 1, you could do it like this...

[Final Category]

{FIXED [Key]: MAX(IIF([Check Code]=1,[Category],NULL))}

So this running a row-level calculation (the IIF part) and only populating a row if  [Code Check] = 1 (else NULL). We then use a FIXED LoD to take the MAX (for each [Key])...and the MAX of something and NULL is always the something!! (in fact the MIN of something and NULL is always the something too!!).

If we didn't always have a 1, we could generate the MIN of each Key's [Code Check] with a further LoD...so would be

{FIXED [Key]: MAX(IIF({FIXED [Key]: MIN([Check Code]={FIXED [Key]: MIN([Check Code])},[Category],NULL))}

a bit harder to follow!, but you can create a table and bring the parts in bit-by-bit to see what each is doing.

Once we have this, it's the same as before....new version attached.

1 of 1 people found this helpful
• ###### 6. Re: Counting distinct by category

Hi Simon

Thank you so much for your help!

I made slight changes to the code for the second scenario.

{FIXED [Key]: MAX(IIF(([Check Code])={FIXED [Key]: MIN([Check Code])},[Category],NULL))}