Grouping unique values into buckets for interface pie chart

Question

I'm really hoping this can be accomplished without needing to create yet another formula field, so I'll give details below and see where I get. 🙂

I have fields for start and end dates and then a formula that calculates the duration between them, including when the end date is blank and then it just keeps counting from the start. I want to have a chart show this data on an interface, but I don't want every single value showing uniquely on the chart if possible.

Instead, I'd like to have it group them into buckets for things like 0-30 Days, 31-90 days, 90+ days, but I can't see any way to do that intelligently within the interface. My fear is that I'm going to need a formula field that gathers this at the table level and then point the pie chart to that field instead of the duration field.

Anyone have a suggestion for the best way to accomplish this kind of reporting? Thanks!

Sistema_Aotearo · Accepted Answer

I think you're right. You'll most likely have to make a formula to manually group your data before you can display it in the chart. I don't think the grouping functionality within the grid views would be able to accomplish this with you manually creating a formula to group them by. I'm glad Airtable gives us the tools to build these seemingly arbitrary conditions.

The formula shouldn't be too hard to make if you utilize the new Single Select outputs and use a SWITCH()

🙂

Mouser-IB-Admin · Answer

I think you're right. You'll most likely have to make a formula to manually group your data before you can display it in the chart. I don't think the grouping functionality within the grid views would be able to accomplish this with you manually creating a formula to group them by. I'm glad Airtable gives us the tools to build these seemingly arbitrary conditions.

The formula shouldn't be too hard to make if you utilize the new Single Select outputs and use a SWITCH()

🙂

Thanks, I ultimately broke down and made the formula field yesterday afternoon and it works great. Is it yet another field? Yes. Does it get the job done? sigh Yes. With Airtable having a limit on the number of fields you can have, I really wish they'd stop having the workaround for nearly every issue be to just create another formula field. For the record, my final formula is below.

IF(
  {Ticket Duration},
  IF(
    {Ticket Duration} <= 30, 
    "0-30 Days", 
    IF(
      {Ticket Duration} <= 90, 
      "31-90 Days", 
      IF(
        {Ticket Duration} <= 180, 
        "91-180 Days", 
        "180+ Days"
      )
    )
  ),
  BLANK()
)

I then formatted the results as single selects, so below you can see that on the left is the original field with the actual number value for each record, and on the right is the output from the formula which groups them into buckets.

Which gives the end result of a much cleaner pie chart. 🙂

Hopefully sharing these details will help someone else. The Airtable community is awesome and I'm happy to be a part of it.

Sign up

Login to the community

Scanning file for viruses.

This file cannot be downloaded