2 Replies Latest reply on Feb 6, 2017 3:27 AM by Roberto Alvarez

    Automatic date conversion in Cube: month and day swapped the 1st 12 days of each month

    Roberto Alvarez

      Dear all,

       

      when connected to a cube, I change the the date dimension from "default" (string) to "date", Tableau confuses days with months for the 1st 12 days of each month.

       

      I have tried changing the locale, but hasn't worked so far. In fact, if I set USA locale (by default I have European), then the first 12 days are correct, but months and days are swapped from day 13th onward for each month (the problem is reversed).

       

      The alternative is work with the date as a string, but it is much less convenient..

       

      Any solutions?

       

      Many thanks in advance

       

      Here is a snapshot:

        • 1. Re: Automatic date conversion in Cube: month and day swapped the 1st 12 days of each month
          Santiago Sanchez

          Hi Roberto,

           

          You should be able to create a calculated member that sets up the date in a particular format that tableau recognized correctly, mm-dd-YYYY for instance, even when your cube stores it at dd-mm-YYYY. Here's How to Create a Calculated Member

           

          Dates in my cube (SSAS) are formatted differently to yours, they have this string format: dd/mm/YYYY, which I've used as a base for the following calculated member. For this example I've replaced the '/' with '-', but you can also change the order of the date parts for your cube:

           

          cube.png

           

          LEFT([Order Date].[Date].CurrentMember.MemberValue, INSTR(STR([Order Date].[Date].CurrentMember.MemberValue), '/')-1)

          + "-" +

          LEFT(

               RIGHT([Order Date].[Date].CurrentMember.MemberValue,  LEN([Order Date].[Date].CurrentMember.MemberValue) - INSTR(STR([Order Date].[Date].CurrentMember.MemberValue), '/')),

               INSTR(RIGHT([Order Date].[Date].CurrentMember.MemberValue,  LEN([Order Date].[Date].CurrentMember.MemberValue) - INSTR(STR([Order Date].[Date].CurrentMember.MemberValue), '/')), '/')-1

          )

          + "-" +

          RIGHT([Order Date].[Date].CurrentMember.MemberValue, 4)

           

          dates.png

           

          There's probably a simpler MDX statement (if there's such thing as a simple MDX statement)... my MDX is just rusty

           

          Hope this helps!

          1 of 1 people found this helpful