Jul 16 2020 09:30 AM
I need help using a query to convert Fiscal year to Calendar Year. For Example, I have the Fiscal_Year column and Fiscal_Quarter Column, as shown below
Fiscal_Year | Fiscal_Qtr | Calendar_Year |
FY21 | 4Q | |
FY22 | 4Q | |
FY21 | 2Q | |
FY24 | 4Q | |
FY24 | 2Q |
I need help coming up with a query of converting the Fiscal year to normal calendar dates group of Month/Date/Year(mm/dd/yyyy). if it is Fiscal_Year FY21 and Fiscal_Qtr is 4Q, then the date should be 9/30/2021, and if it is Fiscal_Year FY21 and Fiscal_Qtr is 2Q then the Calendar_Year should be 06/30/2021
Jul 18 2020 09:45 AM
@amasikonde
As a general rule, I prefer table-driven solutions for requirements like this.
A simple "tally table" of Fiscal Quarter and Fiscal Month (with last day of month) would be my choice for this one.
I.e. Fiscal Quarter 4 ends on September 30th, Fiscal Quarter 1 ends on March 31st. So, if you are looking for the ending date of the 4th Fiscal Quarter of 2022, you can join that table to the other table(s) you need to work with and use it to calculate the date.
While tasks like this can be accomplished with VBA functions, I just think it's better to go with the simplest solution that works.
Jul 20 2020 06:17 AM
how do I combine these in one column in access IIF([Sales Projection Year)=FY21, 2021or IIF([Sales Projection Year)=FY22, 2022 or IIF([Sales Projection Year)=FY23, 2023. Basically I just want this equation to return a full year instead of Fiscal year.@George Hepworth
Jul 20 2020 06:38 AM
I would, again, use a Tally Table that maps Fiscal Years and Quarters to Calendar Dates. Then I'd join that to the base table INSTEAD of that Conditional If.
If you can provide a spreadsheet of sample data--real fields as they appear in the actual table--I can put together an example in Access. It's really hard to spin up samples from partial descriptions.
Jul 20 2020 09:08 AM
George, I was actually able to write equations to return the respective columns. for FY i wrote Planned_Year_Sales:iif([Planned Sales Year]="FY21",2021,([Planned Sales Year]="FY22",2022,([Planned Sales Year]="FY23",2023)))
and for the Quarter I wrote Planned_Month_Sales:IIF([Planned Sales Quarter]=Q,12,IIF([Planned Sales Quarter]=Q2,3,IIF([Planned Sales Quarter]=Q3,6IIF([Planned Sales Quarter]=Q4,9))))
and for the date I picked the last date of the month of the Planned_Month_Sales and I wrote Planned_Date_Sales:IIF([Planned_Month_Sale]=12,1,IIF([Planned_Month_Sale]=3,31,IIF([Planned_Month_Sale]=3,6,IIF([Planned_Month_Sale]=4,9))))
Planned Sales Year | Planned Sales Quarter | Planned_Year_Sales | Planned_Month_Sales | Planned_Date_Sales | Actual Planned Date |
FY21 | Q | 2021 | 12 | 31 | |
FY21 | 2Q | 2021 | 3 | 31 | |
FY21 | 3Q | 2021 | 6 | 30 | |
FY21 | 4Q | 2021 | 9 | 30 | |
FY22 | Q | 2022 | 12 | 31 | |
FY22 | 2Q | 2022 | 3 | 31 | |
FY22 | 3Q | 2022 | 6 | 30 | |
FY22 | 4Q | 2022 | 9 | 30 | |
FY23 | Q | 2023 | 12 | 31 | |
FY23 | 2Q | 2023 | 3 | 31 | |
FY23 | 3Q | 2023 | 6 | 30 | |
FY23 | 4Q | 2023 | 9 | 30 |
Now I'm struggling to combine the numbers into a date in "Actual Planned Date" column to come up with example 2021/12/31, I would love and appreciate if you can help me @George Hepworth