7 Replies Latest reply on May 11, 2012 9:26 AM by dhveryoldaccount

    Calculating a pace??

      I'm having a difficult time using the built in functions in Tableau to get a matching pacing number for sales.

       

      Quickly, the way we calculate our pace is to take the sum of our units sold for month to date.

      Divide that number by the number of working days we used to date for the month.

      Mulitply that ratio of units/day by the total number of working days available in the month.

       

      Units sold to date / # of work days to date * # of work days available

       

      I can't find an accurate way to calculate the "# of work days to date" or "# of works days available".

       

      Any ideas? Any help would be greatly appreciated.

        • 1. Re: Calculating a pace??
          Tracy Rodgers

          Hi Derek,

           

          A calculation using the datediff calculation to find the '# of work days to date ' could be used. The formula might look similar to the following:

           

          datediff('day', [date field], today())

           

          This will return the number of days between that starting date and today. I hope this helps!

           

          -Tracy

          1 of 1 people found this helpful
          • 2. Re: Calculating a pace??
            Richard Leeke

            This old thread discusses the issue of working days. I also remember another more general solution involving a separate calendar table so that individual holiday days could also be excluded - but didn't find that one with a quick search.

            1 of 1 people found this helpful
            • 3. Re: Calculating a pace??

              Thanks for the helpful replies.

               

              Here's what I did to find the maximum number of days in a given month.

               

              day(date(datetrunc('month',date(datetrunc('month', today()))+32))-1)

               

              What this does is find the maximum number of days in the current month using the "today()" function inside the "datetrunc" function.

               

              You can test the calculations by just changing "today()" to any specified date surrounded by "#". Like #2/15/2012#.

               

              Now I need to find a way to count the number of Sundays in a given month. Thoughts?

              • 4. Re: Calculating a pace??
                Richard Leeke

                Attached workbook has a couple of slightly convoluted calculations that get you want you want (plus a slightly simpler version of counting days in a month).

                 

                The Sundays one just works by saying that the first 28 days of the month must have 4 Sundays and then working out whether the remainder includes a Sunday for each of the 4 possible month lengths (28, 29, 30 and 31 days).

                • 5. Re: Calculating a pace??

                  Thanks Richard! The Sunday calculation works great!

                   

                  By taking the total number of days in a month minus the number of Sundays gives me the "work days available" for the month.

                   

                  Now I need to find out how many of those work days I've used up to today.

                   

                  Thoughts?

                  • 6. Re: Calculating a pace??

                    Think I figured it out. Please check my work, but I think this solves my problem.

                     

                    iif(day(today())<=7,  (today() - datetrunc('month',today())-1),

                    iif(day(today())<=14,(today() - datetrunc('month',today())-2),

                    iif(day(today())<=21,(today() - datetrunc('month',today())-3),

                    iif(day(today())<=28,(today() - datetrunc('month',today())-4),

                    iif(day(today())<=31,(today() - datetrunc('month',today())-5),

                    0)))))

                    +1

                    • 7. Re: Calculating a pace??

                      Just an update. I've created another pace report for our "service" department. Sales and service have different working days. Sales works Monday - Saturday, service works Monday - Friday. Richards help on finding the number of Sundays in a month, a number I needed to exclude in order to calculate the correct number of "working" days in a month for sales, was invaluable. Now for service I needed to find the number of Saturdays as well, also to exclude from calculations.

                       

                      Quickly, here is how to find Sundays in a given month:

                      4 +

                      CASE [Days in Month]

                      WHEN 28 THEN 0

                      WHEN 29 THEN IIF(DATEPART('weekday',[29th of Month])=1, 1, 0)

                      WHEN 30 THEN IIF(DATEPART('weekday',[29th of Month])=1 OR DATEPART('weekday',[29th of Month])=7, 1, 0)

                      WHEN 31 THEN IIF(DATEPART('weekday',[29th of Month])=1 OR DATEPART('weekday',[29th of Month])>=6, 1, 0)

                      END

                       

                      Where [Days in Month] is: DATEADD('month', 1, DATETRUNC('month',today())) - DATETRUNC('month',today())

                      And where [29th of the Month] is: DATEADD('day', 28, DATETRUNC('month', today()))

                       

                      To find Saturdays in a month I created this (based of Richard's "Sundays" calc):

                      4 +

                      CASE [Days in Month]

                      WHEN 28 THEN 0

                      WHEN 29 THEN IIF(DATEPART('weekday',[29th of Month])=7, 1, 0)

                      WHEN 30 THEN IIF(DATEPART('weekday',[29th of Month])=7 OR DATEPART('weekday',[29th of Month])=6, 1, 0)

                      WHEN 31 THEN IIF(DATEPART('weekday',[29th of Month])>=5, 1, 0)

                      END

                       

                      Attached is an Excel representation for thinking about the 29th of the Month.

                      1 of 1 people found this helpful