3 Replies Latest reply on Nov 1, 2012 10:39 AM by Isadora Paranhos

    Creating a rate based in datefield

    Isadora Paranhos

      Hi,

       

       

      I would like to calcule a rate about cars's location by month.This rate is calculated diving number of days of location by number of days about that month.

      In my database I have date of leave (date that car was located) and date of return(date that car came back).

      My problem is that a car can be located by many months, Its simple to calculated for just one month, to  calculated more than one I would need a while and a variable to store my values and tableau does not support this function or I couldn't found.

       

       

      simple example:

       

      date of leave: 10/20/2012

      date of return: 10/22/2012

      rate: (22-20)/31 = 0.0645

       

      31 is the number of days in october

       

      hard example:

       

      date leave: 10/30/2012

      date return: 11/5/2012

      rate: (31-30)/31 + (5-1)/30 = 1/31 + 4/30 = 0.032 + 0.133 = 0.165

       

      I can't calculated this diferent rates to sum after.

      Could you help me?

       

       

      Thanks!!!

        • 1. Re: Creating a rate based in datefield
          Alex Kerin

          See new post as well

           

          datediff('day',[date leave],[date return]) should give you the elapsed number of days but it seems you need to divide by the number of days in the month as well - a little unusual.

           

           

          Answer: Getting the days in between we need the calculation above:

           

          datediff('day',[Data saída],[Data Retorno])

           

          and we need the days for all of the full months that include the dates. We can get that with:

           

          (datediff('day',datetrunc('month',[Data saída]),dateadd('month',1,datetrunc('month',[Data Retorno])))-1)

           

          In other words, give me the difference between the 1st of the month when the rental started, and the 1st of the month in the month following the return, minus one day (the first)

           

          At first glance, this seems correct, but please check the numbers...

          • 2. Re: Creating a rate based in datefield
            Alex Kerin

            Of course I realized this is wrong as it will always give a number equal to 1 or less. This is better I believe:

             

            if datediff('month',[Data saída],[Data Retorno])=0 then

                datediff('day',[Data saída],[Data Retorno])/

                (datediff('day',datetrunc('month',[Data saída]),dateadd('month',1,datetrunc('month',[Data saída])))-1)

             

             

            else   (datediff('day',([Data saída]),dateadd('month',1,datetrunc('month',[Data saída])))-1)/

            (datediff('day',datetrunc('month',[Data saída]),dateadd('month',1,datetrunc('month',[Data saída])))-1)+

            datepart('day',[Data Retorno])/

            (datediff('day',datetrunc('month',[Data Retorno]),dateadd('month',1,datetrunc('month',[Data Retorno])))-1)

             

             

            + datediff('month',[Data saída],[Data Retorno])-1

            end

            • 3. Re: Creating a rate based in datefield
              Isadora Paranhos

              Hey Alex,

               

              Thanks!!!

              I was studying your sugestions and logical is ok for me, but I woud like values divided by month when location is bigger than one.

               

              In example that I used:

               

              date leave: 10/30/2012

              date return: 11/5/2012

              rate: (31-30)/31 + (5-1)/30 = 1/31 + 4/30 = 0.032 + 0.133 = 0.165

               

              Rateon october is 0.032 and november is 0.133, but I also wanna the somatory.

              I believe you sugestion is the way missing details like that!

               

               

              Thank you so much!