How to summarize revenue based on shift hours?

[Daniel_Goris]

Hi everyone,

I suppose someone else must have dealt with this before so here my question, hope someone can point me in the right direction!

We run a few bars in our property and we connected our POS to Airtable, so every day I get all sales data imported. The data includes the exact moment (date and time) of the sale, as well as total value, tips and so on.

We also use AT for staff planning and payroll so I know who worked exactly when and where.

Now, I want to create a table that reports  how much revenue was made within one shift, based on actual clock-in and clock-out time of the person working. I can then use that for bonus payout calculations and analytic purposes.

I have, within one base, the employee and shift details, as well as all of that sales data.

Does anyone have an idea on how to approach this?

thanks in advance!

[Jack_Manuel]

It's hard to give exact instructions without seeing your base but hopefully I can point you in the right direction.

Assuming your shifts are created ahead of time, and that there is no overlap on shifts for the same bar, one way to go about this would be to set up an automation for when new sales data is created that assigns the sales item to the shift as a linked record. You could then use a lookup field on the shift to sum the net values of the linked sales data.

[Daniel_Goris]

Thanks Jack, few things to sort out still indeed (such as double shifts:-) ) but this was the little push i needed!