Need help with Percentage %

[Jason_Hawkes]

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…

[AlliAlosa]

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 :slightly_smiling_face:

[Jason_Hawkes]

AlliAlosa:

SUM(values)/{Total Jobs}

Hi,

Thanks for this.

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

[AlliAlosa]

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

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

That should work!

[Jason_Hawkes]

Hi,

This is perfect :grinning_face_with_smiling_eyes:

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

Jason…

[AlliAlosa]

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).

Airtable

Monthly Income (Example Base) - Airtable

Explore the "Monthly Income (Example Base)" base on Airtable.

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.

[Pamela_Hannon]

Thank you for these instructions!! They’re the best I’ve found so far!!

[MattJD]

Hi, can you help me? I need to sum 16% if the field “Incluir IVA” contains 16%, if it doesn’t, just do the first arithmetic operation. Thank you