Perform a calculation based on 2 matching criteria

[Michael_989]

Hello,

I am importing records into my airtable via a zap. The application at the other side of the zap allows users to submit their vehicle mileage firstly in the morning and then in the evening. A row/record is created for the start mileage, then another for the end mileage. There is a column for date, user, start mileage, end mileage.

What i would like to achieve is to automatically calculate the daily mileage for each user on each day. So I need to match records that have the same date and user, then from this subtract the start mileage from the end mileage.

Can anybody point me in the right direction if this is possible and if so, how it could be done?

Thanks

Mike

[Kamille_Parks]

If you just want to view the daily mileage, group your table first by date then by user, then use the summary 'rows" at the bottom of the screen.

If you need to manipulate the data, I would add a Link to Another Record type field pointing to a new table. Set your Zap to calculate the value of the field as something like {Date}: {User}. That way each person will get 1 record per date in this new table, and you could use Rollup and Formula fields to calculate the daily mileage for each user. I’d say use two Rollups (one where the aggregation is {Start Mileage} -> MIN(values) to get the starting mileage and one for {End Mileage} -> MAX(values) to get the final mileage). Then your formula field could be a simple subtraction {Field 2} - {Field 1}.

[Michael_989]

Hi Kamille, Thankyou for your help on this. Ive not had chance to look at this yet, but hope to in the next couple of days. Really appreciate your help.

Thanks

mike