Fiscal Year Quarters ( not aligned to calendar Quarters)

[Oldrider]

Help in getting quarters for fiscal year that starts in a month and date other than January 1.

This post has been edited taking into account the response provided by technocrat

suppressPackageStartupMessages (library(tidyverse))
suppressPackageStartupMessages (library(lubridate))
suppressPackageStartupMessages (library(reprex))
suppressPackageStartupMessages (library(stringr))

dtx = as_tibble( data.frame( dt=c("2018-07-15", "2018-12-27", "2019-01-01", "2019-06-01", "2019-07-01", "2019-10-16", "2019-12-31", "2020-01-01", "2020-06-30", "2020-07-01", "2020-11-09", "2021-01-19", "2021-06-30", "2021-07-01"),
expected_fy_q = c("2019 - Q1", "2019 - Q2", "2019 - Q3","2019 - Q4","2020 - Q1","2020 - Q2", "2020 - Q2","2020 - Q3","2020 - Q4", "2021 - Q1", "2021 - Q2","2021 - Q3", "2021 - Q4", "2022 - Q1")))

dtx
#> # A tibble: 14 x 2
#> dt expected_fy_q
#>
#> 1 2018-07-15 2019 - Q1
#> 2 2018-12-27 2019 - Q2
#> 3 2019-01-01 2019 - Q3
#> 4 2019-06-01 2019 - Q4
#> 5 2019-07-01 2020 - Q1
#> 6 2019-10-16 2020 - Q2
#> 7 2019-12-31 2020 - Q2
#> 8 2020-01-01 2020 - Q3
#> 9 2020-06-30 2020 - Q4
#> 10 2020-07-01 2021 - Q1
#> 11 2020-11-09 2021 - Q2
#> 12 2021-01-19 2021 - Q3
#> 13 2021-06-30 2021 - Q4
#> 14 2021-07-01 2022 - Q1

Note: dtx has 2 columns 'dt' and 'expected.fy_q' which shows the fiscal quarter as calculated by Excel, which is what I expect to see. However, using "quarter" as below I get Y.q (2019.4) and it doesn't provide the fiscal year quarters especially the q1( July-Sep) and q2(Oct-Dec)

dtx$fy_q_using_R <- quarter(dtx$dt, with_year = TRUE, fiscal_start =7)

dtx
#> # A tibble: 14 x 3
#> dt expected_fy_q fy_q_using_R
#>
#> 1 2018-07-15 2019 - Q1 2018.1
#> 2 2018-12-27 2019 - Q2 2018.2
#> 3 2019-01-01 2019 - Q3 2019.3
#> 4 2019-06-01 2019 - Q4 2019.4
#> 5 2019-07-01 2020 - Q1 2019.1
#> 6 2019-10-16 2020 - Q2 2019.2
#> 7 2019-12-31 2020 - Q2 2019.2
#> 8 2020-01-01 2020 - Q3 2020.3
#> 9 2020-06-30 2020 - Q4 2020.4
#> 10 2020-07-01 2021 - Q1 2020.1
#> 11 2020-11-09 2021 - Q2 2020.2
#> 12 2021-01-19 2021 - Q3 2021.3
#> 13 2021-06-30 2021 - Q4 2021.4
#> 14 2021-07-01 2022 - Q1 2021.1

As you may notice, dtx$fy_q_usingR[1] is not the same as dtx$expected_fy_q[1]. It is 2018.1 whereas it should be 2019.1. Similarly, dtx$expected_fy_q[5] is 2020 - Q1 whereas dtx$fy_q_usingR[1] is 2019.1

To make it easier to visually compare, I have provided an additional column
dtx$fy_qtr_text <- gsub("\."," - Q",dtx$fy_q_using_R)

Created on 2020-04-15 by the reprex package (v0.3.0)

[technocrat]

Please see the FAQ: What's a reproducible example (`reprex`) and how do I do one? Using a reprex, complete with representative data will attract quicker and more answers. In this case, it's almost complete, but without digging, it's hard to know what package to look for as.yearqtr in.

Here's an alternative

library(lubridate)
#> 
#> Attaching package: 'lubridate'
#> The following objects are masked from 'package:base':
#> 
#>     date, intersect, setdiff, union
x <- ymd(c("2012-03-26", "2012-05-04", "2012-09-23", "2012-12-31"))
quarter(x, with_year = TRUE, fiscal_start = 11)
#> [1] 2012.2 2012.3 2012.4 2013.1

^{Created on 2020-04-08 by the reprex package (v0.3.0)}

[Oldrider]

Thanks. I have reuploaded the query , I used the solution you provided but I'd like to see what other alternatives are there.

Also, instead of 2012.2 how can I get 2012 Q2

Thanks

[technocrat]

@Oldrider

2020.4

suppressPackageStartupMessages(library(stringr))

i_have <- "2020.4"
offends <- "\\."
desired <- "-Q"
str_replace(i_have,offends,desired) -> i_want
i_want
#> [1] "2020-Q4"

^{Created on 2020-04-08 by the reprex package (v0.3.0)}

[Oldrider]

I have reuploaded a larger dataset but am getting incorrect outputs. Can someone advise why this is so?