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

    Total days in date range as a constant

    Amy Song

      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!