4 Replies Latest reply on Sep 11, 2018 12:48 PM by Deepak Rai

# Count Distinct Given a Specific Condition

Hello Everyone!

My goal is to calculate an average value over a given time frame. Normally (in my specific situation) it would be "Number of Quotes" / "Number days" and the filters I select would take care of the rest.

SUM([Quote Count]) / COUNTD([Quote/Sell Date])

HOWEVER, my boss wants something a little different.

He wants to divide by the number of business days. The weekend work that some of the sales team works on is only bonus that can boost the average. The number of business days that have passed in my specific time frame is difficult to calculate because it always changes. But I think I have a way of calculating the denominator.

In SQL terms, I would do a WHERE clause. COUNT(DISTINCT quote/sell_date) WHERE COUNT (sales people on a given day) >3

I want to do this because on holidays, off days and weekends - it's basically guaranteed that fewer than 3 people will work. And I know if more than 3 people work, it's almost guaranteed a work day (or at least should count as a workday)

However, I don't know how to do this in Tableau. How do I make this work? How can I count the number of NAMES on a specific DATE to see if enough people are working and then count that day as a business day. That way I can count the distinct number of business days over a certain period.

OVERVIEW: I need help calculating the denominator. The denominator is the number of business days over a chosen time interval. I know it's a business day if more than 3 people work (issue a quote). I want to calculate the number days where more than 3 people were quoting.

Message was edited by: Ethan Martin Fake excel file example

Thanks

Deepak

• ###### 2. Re: Count Distinct Given a Specific Condition

Hi Ethan

Here it Is

Denominator is Set to COunt Distinct those days when >2 people work

Thanks

Deepak

If it Helps, Pl mark It Helpful and CORRECT to Close Thread

1 of 1 people found this helpful
• ###### 3. Re: Count Distinct Given a Specific Condition

Genius. Thank you. I didn't think to break this into two part. The calculated date field made all the difference.

• ###### 4. Re: Count Distinct Given a Specific Condition

Thanks Ethan,Please take a Sec to Close Thread by Marking my Reply as CORRECT. The CORRECT button is under my Reply in this main Thread, not in email

Thanks

Deepak