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

# Creating a rate based in datefield

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

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:

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

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])/

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

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

end

• ###### 3. Re: Creating a rate based in datefield

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!