3 Replies Latest reply on Jan 25, 2019 8:55 AM by albert wong

    Calcuate Daily Quota Based on Monthly Figures

    Ryan Blank

      Hello - I have been asked to create a new dashboard with data points that are way above my head.

       

      The idea is to create a dashboard for sales reps showing their rolling month to date sales and measure their progress towards hitting their monthly sales quota. In our dataset, the sales will update daily so there are no issues there.

       

      However, the quotas are in the dataset as a monthly total. I would need to take the number of US business days in a given month, say 21 for January 2019, divide the total quota by 21 to get the daily quota target. From there I need to multiply the daily quota target by the most recent business day so I can calculate the MTD % to Quota field.

       

      I couldn't replicate this with the SuperStore data so here is a screenshot of what I'm working through:

      I'm assuming that I'll also need to load a spreadsheet outlining our business days for the year.

       

      Ideally, the finished product in Tableau would look like this:

      Is this something that can be achieved with custom calculations?

       

      Many thanks,

       

      Ryan

        • 1. Re: Calcuate Daily Quota Based on Monthly Figures
          albert wong

          Hi Ryan,

           

          See attached workbook.

           

          I made 2 tabs.. the example shows how everything looks and what calculations to reference.

           

          The Final Result tab is just that.

           

          First you need to calculate how business day you're on.

           

          Create the following calcs:

           

          First Day (current month)

          DATE(DATETRUNC('month',TODAY()))

           

          Last Day (current month)

          DATETRUNC('month',DATEADD('month',1,TODAY()))-1

           

          Business Day

          IF ((MONTH(DATE(TODAY()))=1) OR (MONTH(DATE(TODAY()))=5)
          OR (MONTH(DATE(TODAY()))=7) OR (MONTH(DATE(TODAY()))=9)
          OR (MONTH(DATE(TODAY()))=11) OR (MONTH(DATE(TODAY()))=12))
          THEN((DATEDIFF('day',[First Day (current month)],[Last Day (current month)]) -(7-DATEPART('weekday',[First Day (current month)])) -
          DATEPART('weekday',[Last Day (current month)])) / 7*5
          + MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))+ MIN(5,(DATEPART('weekday',[Last Day (current month)])-1))-1)
          ELSE
          (DATEDIFF('day',[First Day (current month)],[Last Day (current month)]) -(7-DATEPART('weekday',[First Day (current month)])) -
          DATEPART('weekday',[Last Day (current month)])) / 7*5
          + MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))+ MIN(5,(DATEPART('weekday',[Last Day (current month)])-1))
          END

           

          Current Business Day

           

          IF ((MONTH(DATE(TODAY()))=1) OR (MONTH(DATE(TODAY()))=5)

          OR (MONTH(DATE(TODAY()))=7) OR (MONTH(DATE(TODAY()))=9)

          OR (MONTH(DATE(TODAY()))=11) OR (MONTH(DATE(TODAY()))=12))

          THEN((DATEDIFF('day',[First Day (current month)],[Last Day (current month)]) -(7-DATEPART('weekday',[First Day (current month)])) -

          DATEPART('weekday',[Last Day (current month)])) / 7*5

          + MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))+ MIN(5,(DATEPART('weekday',[Last Day (current month)])-1))-1)

          ELSE

          (DATEDIFF('day',[First Day (current month)],[Last Day (current month)]) -(7-DATEPART('weekday',[First Day (current month)])) -

          DATEPART('weekday',[Last Day (current month)])) / 7*5

          + MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))+ MIN(5,(DATEPART('weekday',[Last Day (current month)])-1))

          END

          -

          ([Business Day] -

          ((DATEDIFF('day',[First Day (current month)],TODAY()) -

          (7-DATEPART('weekday',[First Day (current month)])) -

          DATEPART('weekday',TODAY())

          ) / 7*5

          + MIN(5,(7-(DATEPART('weekday',[First Day (current month)]))))

          + MIN(5,(DATEPART('weekday',TODAY())-1)))+2

          -1)

           

          From there you can start doing you're measures referencing your Current Business Day to get your MTD % to Quota.

          • 2. Re: Calcuate Daily Quota Based on Monthly Figures
            Ryan Blank

            Thank you Albert. This worked perfectly.

             

            Ryan