Formula: Adding a Quarter and Year to a Formula

Hello!

I’m new to Formulas on. Airtable and would love some assistance. I have tried to figure this out through reading the channel and articles and watching videos. I haven’t found the right resource to help me so I’m hoping one of you can.

I already have my NPS calculation on the bottom. I want to use the NPS formula and break it down by quarter and year. I already have a quarter and fiscal year conversion. I have tried multiple formulas and nothing seems to be working to add that into my NPS formula. Thank you!

Screen Shot 2022-05-16 at 10.30.38 AM

Hi Margie, I’d love to help but would need to see how your base is set up before I could provide a solution. Any chance you could provide a screenshot of the column headers? If you could also include example values as well as what the expected output should be that’d be super helpful too

Hi Adam!
Thank you so much for your help! Here are the screenshots of my base’s column headers.

The NPS results should be between 1-10, like the # NPS Score column. However, the Q1 2022 NPS column should only have results from that timeframe. That’s what I’m looking to do. Take the NPS results I already have and filter out just the ones for Q1 of 2022.


Thanks for the details! Hmm, I’m not 100% sure whether this is what you want, but could you check this out? To view the formulas, duplicate the base by clicking the title of the base at the top of the screen and then click the three horizontal dots on the right

The idea is to create a field for each quarter, and then put in a variation of the following formula:

IF(
  {Quarter and Fiscal Year Conversion} = "Q3 - FY'21",
  {NPS Score},
  0
)

Where we’ll just change “Q3 - FY’21” to whatever we need to

Let me know if this isn’t what you’re looking for and I’ll see what I can do!

(p.s. is there any chance the “Grouping” function suffices for your use case?
Screenshot 2022-05-16 at 11.14.23 PM
)


If what I’ve provided is indeed what you’re looking for, could you also check this base out?

While the solution I’ve provided above may solve your problem, this means that you’re going to have to create a new field every quarter. Depending on how often you submit NPSes / what plan you’re on, this latter option might be a nicer way to deal with it.

The idea is to create a new table that just contains the quarters and financial years as individual records, and use a rollup to sum all the relevant NPSes, and it’s reliant on an automation to create the links.

1 Like

Thank you so much for your help! I really appreciate it!
That worked!! =)

I’m glad! Let me know if you have any issues!

This topic was solved and automatically closed 3 days after the last reply. New replies are no longer allowed.