6 Replies Latest reply on Aug 31, 2016 11:10 AM by Mary Solbrig

# Count distinct on aggregated column

Hi

I have a calculated field say CalcField1 as (SUM([Calc Automated1])/SUM([Unique Test Month Id]))*100

I have another measure created as

IF(INT([UniqueAutomated]) >=90)

THEN

"90-100"

ELSEIF  (INT([UniqueAutomated]) >=75)

THEN

"75-89"

ELSEIF  (INT([UniqueAutomated]) >=50)

THEN

"50-74"

ELSEIF  (INT([UniqueAutomated]) >=25)

THEN

"25-49"

ELSE

"<25"

END

Now I create a report for each month for each of the row items which band they fall

sample data

Division     jan     feb     mar     apr     may

abc    50-74     75-89     50-74      25-49     25-49

def     75-89     25-49     25-49     75-89     75-89

ghi     50-74    50-74    25-49      90-100  75-89

I need to create a report wiht the count of band for every month

e.g for jan

50-74 => Count 2

75-89 => Count 1

Thanks

Ajay

• ###### 1. Re: Count distinct on aggregated column

As I understand, the AutomatedGroups are computed per Month and Division, and then for each month, you would like the count of how many times each AutomatedGroup appeared. Is that correct?

Assume that you have a field "AutomatedGroup" defined as above.

1. Create a field called MONTH with formula MONTH(Date) (unless you have 10.0, where calcs are allowed in LOD)
2. Create a new field "Fixed AutomatedGroup" which fixes the level of detail at which to compute the groups:
1. {FIXED [MONTH], [Division]: [AutomatedGroup]}
3. Create a new field called "Count Groups" to count the number of Divisions in each AutomatedGroup per Month:
1. {FIXED [Fixed AutomatedGroup], [MONTH]: COUNTD([Division])}
4. Drag Month(date) to columns shelf, Fixed Automated Group to Rows, and Count Group to Text.
• ###### 2. Re: Count distinct on aggregated column

Thanks Mary, for your prompt response. I am close but for a few "Count Groups" I get +1 count and not able to debug. View data and details does not show any extra Division.

• ###### 3. Re: Count distinct on aggregated column

Hi Ajay,

I think the issue may come from the way you create the buckets in your calculated fields.

IF(INT([UniqueAutomated]) >=90) AND

IF(INT([UniqueAutomated]) <=100 )

THEN

"90-100"

ELSEIF  (INT([UniqueAutomated]) >=75)

AND

IF(INT([UniqueAutomated]) <90 )

THEN

"75-89"

etc....

END

• ###### 4. Re: Count distinct on aggregated column

Changed it as per your recommendation but no luck.

In every column i see +1 for any one column at random and unable to debug

• ###### 5. Re: Count distinct on aggregated column

Hi Mary, Stephan

I changed

{FIXED [Fixed AutomatedGroup], [MONTH]: COUNTD([Division])}

to

{INCLUDE [Fixed AutomatedGroup], [MONTH]: COUNTD([Division])}

and it worked fine. Thanks for your help

Is it correct to use the above INCLUDE and what is the difference - may I know

Thanks

• ###### 6. Re: Count distinct on aggregated column

I'm not sure why the original wasn't working, I'd have to look at the workbook. Include should work just as well.

INCLUDE should work as well. INCLUDE says, "If these fields are not in the viz, use them to compute the field as well, but also use all the other dimensions in the view." FIXED says, "Use these fields and no others to compute the field". This relies a lot on what other fields are in the viz to really say why it behaves the way it does.  some blogs I can suggest:

Understanding Level of Detail Expressions – Part 1 | The Last Data Bender

What’s new in Tableau 9.0? Part 2 – Level of Detail Expressions « Bora Beran

I created an example workbook here with the original solution, if you wanted to compare and figure out why FIXED wasn't working.