Advertec recently encountered a request from a customer to work out driver bonuses based on distance driven as well as the number of tasks they complete.
This was a fairly easy request to accomplish until we realised that that a lot of variables exist such as;
- Drivers can be swapped between vehicles
- Vehicles can stand for periods of times, or be moved from locations without it needing to affect the driver's bonus (positively)
We then realised that odometer readings differed over a period of time (e.g. fort nightly or monthly)
This was fairly simple, Zoho Reports currently uses the same Aggregate Formula to calculate a field to SUM that column,
For Example, if a vehicle's odometer is 1000km at the start of a day, and 1200km at the end of that day, it traveled 200km. But, should the vehicle have moved to a different location (for a Service, or any other reason) the following day's delivery odometer will be 1250km at the start and 1400km at the end of that day (thus driving 150km for the day)
The logic calculation would be that the vehicle drove 350km (200km + 150km) but instead the driven distance over this period of time is worked out as the Maximum odometer of 1400km less the Minimum odometer of 1000km allocating a total of 400km's to the driver's bonus instead of 350km.
That being said, we've suggested an easy workaround:
Export the daily summaries to an Excel Spreadsheet and use the SUM function to calculate the total odometer reading. This way Zoho Reports Aggregate Formula Function will be used to calculate the distance per day, and Excel to calculate the sum of the period.