2 Replies Latest reply on May 10, 2013 9:03 AM by Amy Song

# Total days in date range as a constant

Hi, I want to create a calculated field for transactions per day by vehicle.  So I have a dataset where each row is a transaction (with timestamp).

My view allows the user to select a date range and I have a calculated field that is: sum([Number of Records])/datediff('day',TOTAL(MIN([Date])),TOTAL(MAX([Date]))).

The problem is if the vehicle does not operate every day of the date range, the average is off.  For example, if there are 10 days in the range selected and vehicle A has 10 total transactions, 1 on each day, the calculated field yields 10/10=1 transaction/day.  But if vehicle B skipped a day, and still had 10 transactions over the period of time, the calculated field yields 10/9=1.11 transactions/day.

That is not quite right since the metric I'm looking for should also yield 1 transactions/day for vehicle B; I need the denominator to be constant to the total dataset range even if the vehicle does not operate every day of it (so no existing rows).

Is this possible??  Thank you!

• ###### 1. Total days in date range as a constant

Aren't you looking for:

sum([Number of Records])/datediff('day',MIN([Date]), MAX([Date]))

Or possibly:

sum([Number of Records])/datediff('day',WINDOW_MIN([Date]), WINDOW_MAX([Date]))

But I'm just doing this in my head, and haven't checked any of this. So it's: Just a thought.

--Shawn

1 of 1 people found this helpful
• ###### 2. Re: Total days in date range as a constant

Ahhh... it is actually a combo lol:

datediff('day',WINDOW_MIN(min([Date])),WINDOW_Max(max([Date])))

Thanks!