8 Replies Latest reply on Aug 1, 2013 8:42 AM by Brittany Fong

    mmm-yy format not available

    ruth.wyer

      Hi,

       

      I have found a few threads on this topic but none that seem to answer my issues. 

       

      http://community.tableau.com/thread/111662

      http://community.tableau.com/thread/124268

      http://kb.tableausoftware.com/articles/knowledgebase/supporteddateformats

       

      I have 2 problems with dates:

       

      1 - My datasource stores dates as yyyy-mm-dd.  When this is viewed through Tableau I can convert it to a date but then it appears on my axis in long format e.g. September 2012.  This won't fit on the graph and just appears as "Septe......" so I would like to shorten it to Sep12 or something like that.  I don't seem to have any option under Format.  Automatic is the only option on the drop down.  Using Alias is no good as it converts it to text and then sorts the graphs out of sequence.   How can I shorten the format?

       

      2 - When connecting to a cube, there is no option to change the number format.  I therefore have to change the datasource to present the data in a format that Tableau recognises.  The third link above states that D12 mmmm, yy will present data as Jun, 2009.  Is that a typing error?  Should that be mmm, yy?  If so, what format would the dates in my cube need to be in, in order for me to get a shortened format in my graphs so that all dates can be viewed on the axis?

       

      Thanks

      Ruth

        • 1. Re: mmm-yy format not available
          Andy Harris

          Hi Ruth,

           

          We had problems with our cube source and getting the date formats correct too.

           

          The way around our particular problem was to create a Calculated Member in Tableau that then returns the value specifically as a date. Then when you add that field you get all of the options to format the date as you wish.

           

          In my scenario we applied this formula on the dimension Metric Date.Fiscal Year;

          Capture.JPG

          Then when you add the new field into your worksheet you should gain all of the date format options;

          Capture1.JPG

          1 of 1 people found this helpful
          • 2. Re: mmm-yy format not available
            ruth.wyer

            Oh wow.  That's impressive and would be perfect.  I have not seen calculated members anywhere in Tableau though.  How do you get to that?  I should  mention that we are on v7.0.1.  Really hoping it's not a feature released in later version?

            • 3. Re: mmm-yy format not available
              Andy Harris

              From the worksheet view click on the down arrow next to "Dimensions" and select Calculated Members;

              Capture.JPG

              There are quite a few differences in behaviour when connecting to cubes but mostly there are workarounds. For scenarios such as this, you might have to be extra nice to your IT folks if like me your understanding of MDX calculated member statements is pretty much non existent!

              1 of 1 people found this helpful
              • 4. Re: mmm-yy format not available
                ruth.wyer

                Yep.  completely non existent at my end!  Thanks so much for that.  I have to leave now but I'm going to test it first thing in the morning.  Thanks for your help

                • 5. Re: mmm-yy format not available
                  Prashant Sharma

                  Hi ruth,

                   

                  For changing the format of dates, you have to create some calculated field & do some calculation according to your requirement. If you will provide some sample data or some image then we will do some calculation according to your requirement. You will also use different date functions which will provide you format according to your requirement.

                  However, check this formula to edit the format according to your requirement.

                   

                  mid(datename('month',[Date]),1,3)

                   

                  This changes "January" to "Jan"

                   

                  Thanks.

                  • 6. Re: mmm-yy format not available
                    ruth.wyer

                    Hi Andy,

                     

                    I tried adding a calculated member but could not get it to work.  I was not sure exactly how to structure the formula.  My dimension is simply called Date so i used

                    calcmemb.png

                    Calculation check confirmed it was valid.  I notice you have a format field called 'Dates'.  Mine still says Numbers greyed out

                    format.png

                    • 7. Re: mmm-yy format not available
                      ruth.wyer

                      Hi Prashant.  Thanks for the formula.  I thought I could only create calculated fields against Measures? 

                      • 8. Re: mmm-yy format not available
                        Brittany Fong

                        Hi Andy,

                        I'm trying to follow the post that you made about converting dates so that you can format them in Tableau but I keep getting null values returned.  I'm trying to convert [Dim Lead].[Move In Date Actual].  Attached is what I've typed in.

                        MDXdateformat.png

                        Could you please help me?  My end goal is to be able to use these to determine the number of days between Move In Date Actual and Move Out Date Actual.