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

    Count Distinct Given a Specific Condition

    Ethan Martin

      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