4 Replies Latest reply on Sep 19, 2016 9:23 AM by Bhag Ram

# Count distinct within partition and dates

Hi - I am new to Tableau and learning the ropes.

I am trying to figure out how to count distinct occurences within a partition and dates. I cannot attach workbooks, but attached is an excel with the sample mock up data I have.

In the table: I have ID, sub ID, Flag, Date and count.

I need to create a metric that gives me a count for:

1.  The total number of all unique flag values - basically de-dupe the flag and pick the last date instance within each flag type

2. The total number of unique flag values A - basically count the last date instance of this flag type

The expected results are 3 and 1 respectively. Please see attached excel.

Would appreciate some ideas and pointers on how to perform this calculation.

Thanks much!

• ###### 1. Re: Count distinct within partition and dates

Hi Bhag,

There's a bunch of different ways you could do this.  Attached is an example using LOD to filter records with the max date.  Let me know if you have any questions.

Regards,

Ivan

• ###### 2. Re: Count distinct within partition and dates

Hi Ivan,

Thanks for your email. I could not open the workbook looks like my Tableau version is 9 and the message I got is the workbook is in the current version. Any other way, you could share this or I could open what you sent?

• ###### 3. Re: Count distinct within partition and dates

Hi Bhag,

I can walk you through the process.  It is pretty straightforward.

1.  Create Flag Count: COUNTD([Flag])

2. Create Count of A Flag:  COUNTD(IF [Flag] = 'A' THEN 1 END)

--Steps 3-5 aren't really required, CountD will eliminate dupes, but they demonstrate how to select the record with the  max date which could be helpful if you wanted to source the SubID associated with the record.

3. Create Max Date by Flag:   { FIXED [Flag] : MAX([Date]) }

4. Create Max Date by Flag Filter: [Date] = [Max Date by Flag]

5. Drag Max Date by Flag Filter to filters and select True

6.  Layout per screenshot.

Let me know if you have any questions.

Regards,
Ivan

• ###### 4. Re: Count distinct within partition and dates

Thanks a ton Ivan, will try this out and revert if any questions!