2 Replies Latest reply on May 16, 2014 4:04 AM by kettan

    calculated field date

    Francois La Haye

      I have two field which are integer.

       

      [period] and [yr].

       

      The period is the actual month.

       

      How can I create a calculated field which will be the last day of the month??

       

      Ex  Period 4  yr 2009

       

      Last day of month 30/04/2009

       

      Thanks

       

      Francoisi

        • 1. Re: calculated field date
          Ivan Monnier

          Hello,

           

          I crreated a calculated field as follows (I am using a French version dd/mm/yyy, you may have to adapt to your own date format):

          DATEADD('day',-1,DATEADD('month',1,DATE("01/"+str([Mois])+"/"+str([Annee]))))

           

          "Mois" and "Annee" are integers whcih correspond to your "period" and "yr".

           

          Here are the steps

          - I create a date with the 1st day of Mois and Annee.

          - I add one month to this date

          - I substract one day

           

          With this method I nerver have a problem with 30, 31, 28 or 29 being the last day of the month.

           

          Best regards

          • 2. Re: calculated field date
            kettan

            Here is calculation that only uses integers.

            Integers perform much better than strings and thus something that might be felt.

             

            dateadd('day', -1, dateadd('month', [month], dateadd('year', [year] - 1900, #1/1/1900#)))

             

            Attached workbook version:  Tableau 8.1.7