4 Replies Latest reply on Dec 13, 2016 5:13 PM by Jayna tan

    Concatenated Date and String

    Jayna tan

      Hello,

      I concatenated a date time and string together, changing the date to a string. However, the string is dynamic and i have to reformat the whole thing to a date format with the string attached to it. something like "2016 Act YTD". The Act/Plan and YTD has to be dynamic as in the database, it isn't all the same. I've been stuck at this problem for 3 weeks already and I've attached a work book on how it is suppose to be like. Anyone knows how to solve this issue?

        • 1. Re: Concatenated Date and String
          Maciek La

          Hi Jayna,

          Could you please provide exact information that you would like to achieve on the axis?

          Changing your 'Axis' calculated field to

          STR(year([New Year])) + " " + STR([Plan/Act]) + " " + STR([YTD])

          partially solves the problem but I am not sure if that's what you want to achieve.

          forum_20161212.PNG

           

          as a sidenote - I think it will be work considering showing plan and actuals as a single bar + line. It would help to spot the difference between plan and realisation quicker

          --

          if you find this answer helpful please mark it as 'This helped me' / 'Correct answer' and the bottom of the post

          • 2. Re: Concatenated Date and String
            Jayna tan

            Hi Maciek,

             

            This helped me get the format i want, but is it possible to add a date to the String?? Because now it is in a String format but what we want is for it to be in a date format with the String added to it..

             

            So it's like 2016 (Date format) Plan YTD (String)

             

            Regards,

            Jayna

            • 3. Re: Concatenated Date and String
              Maciek La

              Unfortunately Tableau lacks one of the features Excel has - text() / format() functions.

              I think there are 2 things here:

              1. If you would like this field to act as date (so you can drill down, aggregate, sort etc) then you have to leave it without any string addition (and you can format is as you like in format menu)

              2. If you would like to have custom date string then thigs get more complex. Im case of date+string combo, tableau will most likely format the date part using default format pattern. What you can do (and saw it here in community quite often approach) is to split date field to year, month and date part, format it and then merge. Something like:

               

              Str(

              Left(,4) //as year

              "-"

              Mid(,5,7)

              "-"

              Right(,2)

              )+"additional string"

               

              To make it look better you can replace for example the middle part with datename("month", )

               

              However, using such calculation, you will lose date field functionality (it will not drill down, will be sorted as string, etc.)

              In my practice, I would leave the date field unchanged and separate data using different field which could be used as separator or column. It increases usability and readability of the graphs.

               

               

              1 of 1 people found this helpful
              • 4. Re: Concatenated Date and String
                Jayna tan

                Hi Maciek,

                This really helped me a lot! Thank you so much!!!

                 

                Regards,

                Jayna