3 Replies Latest reply on Aug 15, 2018 11:06 AM by Joe Oppelt

    Change date values to string

    Christopher Ray

       

      I have a filter right now that consists of dates. I want to change those values to read as string. Ex. 6/30/2017 would display as "Measurement Year 3 Month 12", 7/31/2017 --> "Measurement Year 4 Month 1" etc.

       

      I don't want to convert the variable itself to a string though because I will need to also create a line graph to show results over time. However, even in the line graph, I would want the axis to read MY4 M1, MY4 M2, etc. and not the date itself.

        • 1. Re: Change date values to string
          Joe Oppelt

          Make a separate calc, and there will be a 1:1 correspondence between any date and the calc value.  So if you filter on the new calc, you will essentially be filtering on the date too.

           

          If you want to grab the year value from a date, use YEAR([Date field]) (which will return an integer.)  Or use the datepart function:

           

          DATEPART('year',[Date field])

           

          There are similar MIONTH and DAY functions, and there are even more options using the datepart function (such as 'week', and 'quarter', etc.)

           

          Once you have the specific values, you can convert them to strings with the STR function.  In fact, you can embed the date functions inside the STR function:

           

          STR(DATEPART('quarter',[Date Field]) )


          And you can embed that into a larger string calc:

           

          "Measurement Year " + STR(YEAR([Date Field]) ... etc.

          • 2. Re: Change date values to string
            Christopher Ray

            So, the measurement years and months don't line up to the calendar year/month cycle.

             

            Every July is Month 1 and June is Month 12.

             

            July 2014 = Measurement Year 0 Month 1

            August 2014 = Measurement Year 0 Month 2 ....

            June 2015 = Measurement Year 0 Month 12

            July 2015 = Measurement Year 1 Month 1

             

            Can I add/subtract years/months in that formula? In other words, it would be [Year] - 2014 = Measurement Year [#]. Year 2015 - 2014 = Measurement Year 1, etc. And for the Months it would be [Month] +6 = [Output Month]

             

            6/2014 --> (2014-2014) = Measurement Year 0, (6+6) = Month 12

             

            EDIT: Hm, that wouldn't quite work either because half of 2015 is MY1 and the other half is MY2..

            • 3. Re: Change date values to string
              Joe Oppelt

              Yup.  Play with it.  You could do:

               

              "Measurement Year " + STR( YEAR([Date Field]) -2014 ) ... etc.

               

              Or you could make separate calcs first to grab the year and month.

               

              MONTH([Date])+6 - ( IF MONTH([Date]) > 6 than 13 else 0 end)

               

              YEAR([Date]) - 2014 + ( if MONTH([Date] > 6 then 1 else 0 end)

               

              You could actually embed all that inside the STR() functions, or make separate calcs and just do STR([Year Calc]) .  It's just a matter of style.  (I tend to compartmentalize stuff into separate calcs.  It makes it easier to go back a year later and figure out what I was doing in there.  And easier for someone else to pick it up and follow along.)

               

              But Tableau lets you insert all sorts of things into all sorts of places.  Notice that I'm adding or subtracting an IF statement in those two examples above.  (Or, more precisely, the results of an IF statement.)