5 Replies Latest reply on Jul 4, 2016 6:32 AM by ahmed.hasnaoui

    Month Date locale and calculation problem

    ahmed.hasnaoui

      Hi community, this is confusing. I have a date column with only the month. I want to create a proper date dimension from this using this formular: DATE([Time Sheet Start Date] + "/" + "01" + "/" + "2016") The problem: I only get proper values for April. I figured out, this is because of the locale of the data source month which is written in English (January, February, March, etc.). My system is running in German. Tableau is running in English. Now how can I solve this? I can only set the date format but not the locale...I created a calculated field with a CASE statement to "translate" it but this must be easier to solve, any settings I miss? Thank you!

        • 1. Re: Month Date locale and calculation problem
          ahmed.hasnaoui

          See:

           

          DateNameProblem.PNG

           

          I created a sample workbook for better understanding of the problem.

          Also my workaround does not work for march...:(

          • 2. Re: Month Date locale and calculation problem
            Mahfooj Khan

            You can change the workbook locale to Germany. See the screen shot

            Then I've created a calc field like this

            Let me know If you've any query.

             

            Mahfooj

            • 3. Re: Month Date locale and calculation problem
              ahmed.hasnaoui

              Hi Mahfooj,

              thank you for your help!

              I was looking for an answer without creating an extra calculated field for the translation.

              I can change the woorkbooks locale to German and it changes the date formats etc. but it is is not affecting the calculations detecting of month names. This means March/März is still null.

              Looks like a bug to me...Do you think this behavior is legit?

              I worked out another solution by using numbers instead of the month names. So I still need the CASE statement but I don't need to type the month names anymore but instead the relative month number.

               

              If anybody comes across this and doesn't want to type that much:

               

              CASE [Start Date]

              WHEN "January"

              THEN "1"

              WHEN "February"

              THEN "2"

              WHEN "March"

              THEN "3"

              WHEN "April"

              THEN "4"

              WHEN "May"

              THEN "5"

              WHEN "June"

              THEN "6"

              WHEN "July"

              THEN "7"

              WHEN "August"

              THEN "8"

              WHEN "September"

              THEN "9"

              WHEN "October"

              THEN "10"

              WHEN "November"

              THEN "11"

              WHEN "December"

              THEN "12"

              ELSE [Start Date]

              END

              • 4. Re: Month Date locale and calculation problem
                Mahfooj Khan

                I'm not asking you to create any extra calculated fields. If you see the calc field which I've mentioned in the screen shot then you can easily get to know that I've reverse the logic that's It. In your case statement It was ENG to German and copied calc field German to ENG. You can try similar approach.

                • 5. Re: Month Date locale and calculation problem
                  ahmed.hasnaoui

                  yes, I've understood this, but I think that Tableau should be able to convert the date without calculated field from GER to ENG or ENG to GER (or what ever).

                  The workbook I attached is also using the workaround but as it is obviously only a locale issue, I thought that I have missed some settings.

                  I found the settings and it is still showing null values.

                  E.g. My calculated field (which is mandatory for my purpose) looks like this:

                  // (Month = April) + Day + Year NOTE: April (GER) = April (ENG)

                  DATE([Start Date] + "/" + "01" + "/" + "2016") = 01.04.2016

                  But

                  // (Month = May) + Day + Year NOTE: Mai (GER) != May (ENG)

                  DATE([Start Date] + "/" + "01" + "/" + "2016") = Null

                   

                  If I change locale to GER or to ENG it's still the same result, only date format is changed.


                  Again, I was looking for a solution without a second calc. field that translates GER to ENG or reverse