Help

calculation on pivot table

Topic Labels: Base design
293 1
cancel
Showing results for 
Search instead for 
Did you mean: 
hrwatkins86
4 - Data Explorer
4 - Data Explorer
Spoiler

I'm trying to calculate return rate (items received/ items delivered),  but aggregate them by day.  However, there are multiple receiving lines for each event, and only one input for items delivered.  I'd normally do a formula based off a pivot table but I'm not sure how to do that in Airtable.

Here's an example of the table I have

 Event NameItems ReceivedItems Delivered 
Timestamp 1x1040 
Timestamp 2x20  
Timestamp 3y4050 

What I need is Line 1 & Line 2 added together for Items  Received, and divide that number by line 1 of items delivered.

 

How do I achieve this?

 

1 Reply 1
Arthur_Tutt
8 - Airtable Astronomer
8 - Airtable Astronomer

Hey @hrwatkins86 do your event names have consistent and predictable names? I'd imagine you could just create a view that filters by todays date (or date you want to check) and filters based on the event names. Check this out:

 Table Setup:

Screenshot 2023-11-01 201208.png

 

Desired Output in New View - Sums of Items Received And Delivered:

Screenshot 2023-11-01 201230.png

 

Filter Conditions - note: use the 'add condition group'  option at the bottom, to encapsulate both Events with an OR statement (Event 1 or Event 2)

Screenshot 2023-11-01 201314.png

Finally, add a grouping based on date to have Airtable sum the values.

Screenshot 2023-11-01 201331.png

 

Let me know if that worked for you!!