10 Replies Latest reply on Mar 3, 2016 12:47 AM by Paul Sherras

    Formatting how 'month' displays on a string date

    joe.rodriguez.3

      Hey guys,

       

      On the attached workbook, can you format the month to be displayed as, for example, Jan 2 - Jan 9 in the week name view? Instead of January 2 - January 9.

       

      If anyone can think of a way to make it simpler to drill down to a weekly look than this, please share any best practices you might have! I plan on having a filter for year, month and week on the side.

       

      Any help would be appreciated. Thanks guys!

       

      Joe

        • 1. Re: Formatting how 'month' displays on a string date
          Rody Zakovich

          Hey Joe,

           

          Can you give more background on what the intended use if for the Dashboard? How we set this up is determined by the use case.

           

          Also, when dealing with Weeks, and Drill downs, you usually run into "Overlapping" time periods. For example in your viz....

           

           

          The reason is because the "Week" exists in both Months (Technically)

           

          You can see if here more clearly by drilling down from the Date Field

           

           

          So how do you want to address this issue? Because that can change the scope of things as well.

           

          Regards,

          Rody

          2 of 2 people found this helpful
          • 2. Re: Formatting how 'month' displays on a string date
            Paul Sherras

            Hi Joe,

             

            You can use a left calculation to only take the first 3 letters of the month ( I have attached your workbook again with this amendment):

             

            LEFT(DATENAME('month',[Week of]),3) + " " +STR(DATEPART('day',[Week of]))+ "-" +

            IF DATEPART('month',[Week Ending]) = DATEPART('month',[Week of])

            THEN ""

            ELSE LEFT(DATENAME('month',[Week Ending]),3) + " "

            END

            +STR(DATEPART('day',[Week Ending]))

             

            Regarding the drilldown, I personally would go with a calculated field and a parameter. I have attached a sample workbook I previously amended for someone demonstrating such use of parameters to gain this affect, albeit in a slightly different way. If you need help setting this up for your criteria let me know, but all the formulas are in the workbook.

             

            Kind regards,

             

            Paul

            2 of 2 people found this helpful
            • 3. Re: Formatting how 'month' displays on a string date
              joe.rodriguez.3

              I knew you would jump on this one Thanks Rody

               

              So this particular user focuses mostly on a weekly totals, starting on Mondays for each company. That's what I need to answer very quickly. Occasionally, they want to see month totals, so by simply drilling out they can get that. But the only drill downs should be Year, Month and Week for ease of use, imo.

               

              So the overlap could be explained to them, but it's probably best to avoid it for simplicity sake. Ideally a display of Jan 1- Jan 7 would be best, with no overlap. I'm totally open to ideas of course.

               

              Joe

              • 4. Re: Formatting how 'month' displays on a string date
                Rody Zakovich

                Hey Joe,

                 

                So here is how I would approach it.

                 

                Step 1. Create a Parameter that will be used to drill down

                 

                 

                Step 2. Create a calculated field that works the parameter and gives you the format you are looking  for

                 

                Drill Down Date

                CASE [Drill Down]

                WHEN 'year' THEN STR(YEAR([Last Update Date]))

                WHEN 'month' THEN STR(YEAR([Last Update Date])) + '-' + LEFT(DATENAME('month', [Last Update Date]), 3)

                ELSE STR(YEAR([Last Update Date])) + '-' +STR(DATEPART('week', [Last Update Date]))

                END

                 

                Step 3. Create two calculated fields that will be used in the Tooltip to display the Starting and Ending Dates of the Period.

                 

                Start Date

                { FIXED [Drill Down Date] : MIN(DATETRUNC([Drill Down], [Last Update Date] )) }

                 

                End Date

                DATEADD('day', -1, DATEADD([Drill Down], 1, { FIXED [Drill Down Date] : MIN(DATETRUNC([Drill Down], [Last Update Date] )) }))

                 

                Add these to the Detail Shelf.

                 

                Now your user can drill down (Or up) but they still get context of the Days in that Period

                 

                 

                 

                Sry couldn't seem to show the tooltip on Screenshot, but if you look at it in the Ex WB, you'll notice it is dynamic based on the Drill Down parameter.

                 

                Hope this helps.

                 

                Regards,

                Rody

                1 of 1 people found this helpful
                • 5. Re: Formatting how 'month' displays on a string date
                  joe.rodriguez.3

                  It looks like you both recommend the same idea. I'll give that a whirl. Rody, the end user (high level execs) wouldn't know what week 54 would be exactly. Can we label that Mar 1- Mar 7? Even if we just label the first monday of the week, that would do. So just Mar 1.

                   

                  Thanks for the support guys. Just another reason why tableau is so awesome.

                   

                  Joe

                  • 6. Re: Formatting how 'month' displays on a string date
                    joe.rodriguez.3

                    We should be able to give the right answer vote to multiple people!

                    • 7. Re: Formatting how 'month' displays on a string date
                      Rody Zakovich

                      Hello Joe,

                       

                      I understand, that is why I added the Dynamic Start and End Date Calcs. That way as soon as they hover over a Bar, The tooltip shows them the time period (Days) in that period.

                       

                      I didn't want to use the Mar 1 - Mar 7, because you will run into the problem I described above...especially considering the Order is not right

                       

                       

                      By using the Week Number (Separate) we avoid that confusion.

                       

                      OF COURSE....it all depends on your audience lol

                       

                      Best regards,

                      Rody

                      1 of 1 people found this helpful
                      • 8. Re: Formatting how 'month' displays on a string date
                        joe.rodriguez.3

                        Thanks Rody, I'll use this setup. Congrats on the spotlight

                        • 9. Re: Formatting how 'month' displays on a string date
                          Rody Zakovich

                          Thanks Joe! Appreciate that.

                          • 10. Re: Formatting how 'month' displays on a string date
                            Paul Sherras

                            Hi Joe and Rudy,

                             

                            Apologies  I don't have the opportunity to check this works, however from my understanding it "should" work.

                            If you amend Rudy's formula to add the following on the bottom:

                             

                            CASE [Drill Down]

                            WHEN 'year' THEN STR(YEAR([Last Update Date]))

                            WHEN 'month' THEN STR(YEAR([Last Update Date])) + '-' + LEFT(DATENAME('month', [Last Update Date]), 3)

                            ELSE LEFT(DATENAME('month',[Week of]),3) + " " +STR(DATEPART('day',[Week of]))+ "-" +

                            IF DATEPART('month',[Week Ending]) = DATEPART('month',[Week of])

                            THEN ""

                            ELSE LEFT(DATENAME('month',[Week Ending]),3) + " "

                            END

                            +STR(DATEPART('day',[Week Ending]))

                            END

                             

                            Then create another field with the following formula:

                            IF [Drill Down] = 'year'

                            THEN DATETRUNC('year',[Last Update Date])

                            ELSEIF

                            [Drill Down] = 'month'

                            THEN DATETRUNC('month',[Last Update Date])

                            ELSE DATETRUNC('week',[Last Update Date])

                            END

                             

                            Put this date field on your Columns and add the YEAR and MONTH date parts. Click on each of these pills and hide the header. This "should" order the week dates in the correct order and prevent duplicate months (as it is truncing to week if week is selected therefore there is no "overlap" between months.

                             

                            Let me know if this works

                             

                            Kind regards,

                             

                            Paul