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!
Accepted Solutions
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?
)
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.
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?
)
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.
