Mar 08, 2019 01:58 AM
Hi there,
I have started using the Blocks on Airtable and i’m in love with them.
I am trying to get a Dashboard with results and compare them with past years and quarters.
To do so i have a ‘Created Day’ column, then another column with a formula which breaks the Quarter and another for the year. Then i Concatenate them and all good and happy days. The issue i’m facing is that I would like to have the Financial Quarter and Financial Year Column.
For the Financial Quarter i kind of have it by doing the below, but i’m sure it could be neater:
IF({Quarter Hidden}=1, “4”)
IF({Quarter Hidden}=2, “1”)
IF({Quarter Hidden}=3, “2”)
IF({Quarter Hidden}=4, “3”)
My main challenge now is to have the formula for the financial year, which runs from April to March, i would like to name it 19/20 for instance. Or the year ahead. So this way i could have my lovely blocks with Financial Year and Quarter.
And lastly concatenate the Financial Quarter with the Financial Year: Q4 - 19/20
Many thanks in advance for your help,
A
Mar 08, 2019 04:09 PM
Here’s an alterate version using SWITCH()
:
SWITCH({Quarter Hidden}, 1, "4", 2, "1", 3, "2", 4, "3")
If I understand the rest of your request, you’re trying to take the date in the {Created Day}
field and break that down into Financial Quarter and Financial Year, then build that label you listed. Is that correct? I’m just a little unclear on the source of the date that will be used for those calculations. So for example, if the date in {Created Day}
is 5/1/19, that would turn into Q1 - 19/20. Is that right?