6 Replies Latest reply on Sep 18, 2017 1:30 PM by Justin Larson

    Count number of Mondays, Tuesdays etc.

    Elie Rahi

      Is there a way, in Tableau, to count the number of each day of the week between two date ranges? For example count how many Mondays between 1 Jan 2017 and 31 August 2017.

        • 1. Re: Count number of Mondays, Tuesdays etc.
          Shinichiro Murakami

          Hi Elie,

           

          You ca refer this link.

           

          Excel's Networkdays Alternative (Count days excluding Weekend) [version 2]

           

           

          [Saturday Count ]

          ceiling(max(

          0,

          datetrunc('week',[End Date],"Saturday")+1-datetrunc('week',[Start Date]+6,"Saturday")

          )/7)

           

          [Sunday Count]

          ceiling(max(

          0,

          datetrunc('week',[End Date],"Sunday")+1-datetrunc('week',[Start Date]+6,"Sunday")

          )/7)

           

           

          Thanks,

          Shin

          << Ambassador Spotlight Here ! >>

          • 2. Re: Count number of Mondays, Tuesdays etc.
            Jennifer VonHagel

            Hi Elie,

             

            Sure, you can find the weekday name of the date, and count distinct dates if they are Monday and in your date range.

             

            COUNTD(

                IF [Order Date] >= #01-01-2017#

                    AND [Order Date] <= #08-31-2017#

                    AND DATENAME('weekday',[Order Date]) = 'Monday'

                THEN [Order Date]

                END

            )

             

            Hope this helps,

            Jennifer

            1 of 1 people found this helpful
            • 3. Re: Count number of Mondays, Tuesdays etc.
              sudheer.kumar.5

              Hope the Jennifer reply helps.

              • 4. Re: Count number of Mondays, Tuesdays etc.
                Elie Rahi

                Hi Jennifer,

                 

                Thanks for your response.

                 

                How can I incorporate this into a formula? My formula is as follows;

                 

                SUM(Number of Records) / ([Monday count]*14+ [Tuesday count]*12+ [Thursday count]*6 + [Friday count]*14+ [Saturday count]*4+ [Sunday count]*4)

                • 5. Re: Count number of Mondays, Tuesdays etc.
                  Jennifer VonHagel

                  Hi Elie, you can do it like this. I noticed there is no clause for Wednesdays, so those dates won't be counted.

                   

                  SUM([Number of Records]) / (

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Monday' THEN [Order Date] END) *14 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Tuesday' THEN [Order Date] END) *12 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Thursday' THEN [Order Date] END) *6 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Friday' THEN [Order Date] END) *14 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Saturday' THEN [Order Date] END) *4 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Sunday' THEN [Order Date] END) *4

                  )

                   

                  You could make each day's count its own calculated field if you want, so:

                  [Monday count] : COUNTD(IF DATENAME('weekday',[Order Date]) = 'Monday' THEN [Order Date] END)

                   

                  And then:

                  SUM([Number of Records]) / (

                  [Monday count] *14 + [Tuesday count] *12, etc, etc,

                  )

                   

                  Finally, I kind of assumed your date range will be filtered in the view, and not need to be in the formula, but just in case it does, you can do this:

                  Add this clause in all your formulas: [Order Date] >= #1/1/2017# AND [Order Date] <= #8/31/2017#

                  SUM([Number of Records]) / (

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Monday' AND [Order Date] >= #1/1/2017# AND [Order Date] <= #8/31/2017# THEN [Order Date] END) *14 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Tuesday' AND [Order Date] >= #1/1/2017# AND [Order Date] <= #8/31/2017# THEN [Order Date] END) *12 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Thursday' AND [Order Date] >= #1/1/2017# AND [Order Date] <= #8/31/2017# THEN [Order Date] END) *6 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Friday' AND [Order Date] >= #1/1/2017# AND [Order Date] <= #8/31/2017# THEN [Order Date] END) *14 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Saturday' AND [Order Date] >= #1/1/2017# AND [Order Date] <= #8/31/2017# THEN [Order Date] END) *4 +

                  COUNTD(IF DATENAME('weekday',[Order Date]) = 'Sunday' AND [Order Date] >= #1/1/2017# AND [Order Date] <= #8/31/2017# THEN [Order Date] END) *4

                  )

                   

                  Hope this helps!

                  Jennifer

                  • 6. Re: Count number of Mondays, Tuesdays etc.
                    Justin Larson

                    This is how I would approach it.

                     

                    Based on original post, I would point out, just in case it's not clear, the formulaic approach counts, for example, the Mondays that appear in your data. Not every Monday on the calendar, which is a fundamental difference when comparing to something like Excel's NETWORKDAYS() function.