3 Replies Latest reply on Jul 17, 2013 3:52 PM by bha.jand

    How to format the Date Using available Tableau Date Fuctions

    Sampath Rao Dheekonda

      Hi All,

       

      I'm able to format the date to display as 'Mmm-yy' format by formatting the date dimension, but unable to achieve the same through a calculated field. Can anyone please let me know how to create the calculated field to get the required format and sorting has to happen  dynamically(means output should be in "Date" data type)


      Ex: By Creating the Custom Date with Month/Year selection I am getting like this : July 2013

             Required Format is : Jul-13


      Appreciate your help. Thanks in Advance.

       

      Regards,
      Sampath

        • 1. Re: How to format the Date Using available Tableau Date Fuctions
          Kishore Kumar Suthar

          You have the date like July 2013 than try this,

          MID(DATENAME('month',[Date]),1,3)+"-"+ MID(DATENAME('year',[Date]),3,4)

          tab.jpg

          tab1.jpg

          • 2. Re: How to format the Date Using available Tableau Date Fuctions
            Mark Holtz

            Hi Sampath,

             

            To add onto what Kishore gave you, creating a string will obviously muck up your sorting. (Feb comes before Jan alphabetically). To get around this, you can include the actual (continuous) Month-year set as a discrete dimension (make it a blue pill on your shelf) in front of the calculated field that makes the string value. Then, just right click the blue Month pill and uncheck "Show Header." Now the field will be present to sort, but won't be visible.

             

            If you need further assistance, please post a packaged workbook.

            Cheers,

            Mark

            • 3. Re: How to format the Date Using available Tableau Date Fuctions
              bha.jand

              Hi Mark,

               

              Thank you for sharing your idea. It is another thing that I learnt new today.

               

              Hi Sampath,

               

              I agree on Mark for sorting issue for a string formatted date column and here is my suggestion for handling the sort.

               

              Since, I have a case where I should consider the hierarchy for month-date formatted to string, I usually sort as per the actual date field. This can be done using: right click on the <Formatted Month Column> or in your case the RequiredDate field and click on sort: here under Sort by, I select the Field option, then select the actual date column and for Aggregation: I select Minimum(for Ascending order for date). This way, I always have my string formatted date in the ascending order.

               

              Hope this helps.