Need help with Percentage %

#1

Hello,

I have a table where I track contractors for attendance within an SLA.

I have SLA Due date and time and Site Attendance date & Time. I have then have a formula column which tells me if the job is pending, Missed or Met.

What I now need is to calculate % of SLA being met for each contractor,

Can someone advise a good method for achieving this?

Jason…

0 Likes

#2

As long as your table is linked to another table that lists each contractor…

Add a column to the same table where you track attendance, with the formula:

IF({Job Status} = "Met", 1, 0)

Then, on the Contractors table, you’ll want to add two fields:

First, a Count field that counts the number of linked records for a contractor, say it’s called {Total Jobs}

Then, a rollup field that looks at {Job Status} from your other table, with the formula:

SUM(values)/{Total Jobs}

Then change the formatting of the field to be a percentage. That should work! Let me know if you need any help :slight_smile:

1 Like

#3

Hi,

Thanks for this.

I have implemeted and all the functions work correctly, however, the percentages dont seem to add up correctly?Percent

0 Likes

#4

Oops! My apologies, I forgot a crucial step. Change the Rollup formula to be

(SUM(values)/{Total Jobs})*100

That should work!

0 Likes

#5

Hi,

This is perfect :smile:

One final Q - I will be looking to break this down into monthly periods. Is this something I can tackle within Airtable too?

Jason…

1 Like

#6

You bet!

There’s a couple ways to tackle it, depending on how you want your end result to look.

You can either create another table where each record is a month, and link your others tables to it manually - see below example base (it deals with income/expenses but the idea is the same).

Or, you can link each record to a single record on a new table, and use conditional rollups to separate out your data by month.

For example, on your Jobs table, add a formula field with the following:

IF(AND(MONTH({Date}) = 1, YEAR({Date}) = 2019), {Data You Want on Other Table}, 0)

This will allow you to rollup data from the month of January 2019 on a new table.

0 Likes