4 Replies Latest reply on Oct 16, 2013 12:16 AM by John Swimming

    Correct way to specify absolute dates in formulas

    John Swimming

      Maybe I am doing something wrong, but I noticed I have to change static dates from a 'YYYY-MM-DD' to a ''MM-DD-YYYY' when dealing with databse replacement between Oracle and MySQL.

       

      Am I forgetting something? If not, is there anyway to define dates universally? Maybe programatically thru Tableau like Tableau_DATE(15,5,1984) . I checked in data types and in functions sections but I did not found anything on this.

        • 1. Re: Correct way to specify absolute dates in formulas
          Ramon Martinez

          I think that the function DATE(date dimension)  work as you require.

           

          Check the attached workbook where I use a simple data set with two dimensions ID and OriginalDate. OriginalDate is an string variable with values in the format "YYYY-MM-DD"

           

          I create a calculated field NewDate using the formula DATE([OriginalDate]) and the result is a DATE dimension in standard format.

           

          This solve your requirement?

           

          Best,

          Ramon

          • 2. Re: Correct way to specify absolute dates in formulas
            John Swimming

            Thanks Ramon!

             

            I could not test your file as it lacks the accompaining CSV. But I am pretty sure it should not work if I test it in MySQL and Oracle judging by your explanation.

             

            I am using DATE('String') already, I found that when replacing the database, I need to change the format of the string. I guess DATE is using the low-level database DATE function, so it would explain why it needs to be customized depending on the database.

             

            Or am I doing somethign wrong? I was using "YYYY-MM-DD" on MySQL

             

            DATE(STR(YEAR(TODAY()))+"-1-7") for 7-Jan

             

            and had to change it to

             

            DATE("7/1/"+STR(YEAR(TODAY()))) when moving to Oracle.

             

            Perphaps is a particular deployment error on my side, or a misconfiguration of some of the databases. Is this normal?

             

            Pero te agradezco muchísimo que hayas tenido la paciencia de ayudarme !

            • 3. Re: Correct way to specify absolute dates in formulas
              Ramon Martinez

              Maybe the best solution in your case is to configure the date format in MySQL the same way it is configured in Oracle.

               

              The other alternative is to work with data conversion in Tableau but that requires to change the formula when you change the data source from MySQL to Oracle.

               

              Spanish:

              Es un placer ayudarle.

              Quizás la solución, en su caso, sea configurar el formato de la fecha en MySQL de la misma manera que está configurado en Oracle.

               

              La otra alternativa es usar campos calculados para manejar la conversión de los formatops de fecha, en ese caso se puede tener dos campos calculados, uno que funciona con MySQL y otro con Oracle, o tener un solo campo calculado que deberá modeificarse su f'rmula una vez cambies la fuente de datos de MySQL a Oracle.

               

              Saludos,

              Ramon

              • 4. Re: Correct way to specify absolute dates in formulas
                John Swimming

                Then it's my database configuration. Thank you!

                That double calculated field is what I was currently doing.

                 

                Cuídate!