Calculate A Quarter Using Fiscal Year

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I am using a date field for completion of a project. I would like a calculated field to establish the right quarter. I did manage to find a solution but that does not necessarily work for my situation.  We Have A FY from Jul to June. And for a quarter we use the last Sunday in the third month of the quarter.  Would someone have a solution for this?
 
Following is formula I am hoping to amend which was created by Joe Y in QB Support. Thank you Joe.

If(Month([Date])>=1 and Month([Date])<=3, "Q1 " & Year([Date]),
Month([Date])>=4 and Month([Date])<=6, "Q2 " & Year([Date]),
Month([Date])>=7 and Month([Date])<=9, "Q3 " & Year([Date]),
Month([Date])>=10 and Month([Date])<=12, "Q4 " & Year([Date]))
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Photo of Jeff

Jeff

  • 20 Points
Hi Arshad,

Does your FY start on July 1st or the last Monday of June?

-Jeff
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Thanks Jeff.  The FY starts on the first Monday in end June. For example, this year our FY starts on 27 June.  meaning that last Sunday is the end of the month.
Photo of Jeff

Jeff

  • 20 Points
Hi Arshad,

Give this formula a try. Let me know how it works for you.

Case(true,
//Q1
[Date]>=FirstDayOfWeek(FirstDayOfMonth(ToDate("7/1/"&Year([Date]))))+Days(1) and
[Date]<=FirstDayOfWeek(LastDayOfMonth(ToDate("9/1/"&Year([Date])))), "Q1 "&Year(AdjustYear([Date], 1)),

//Q2
[Date]>=FirstDayOfWeek(FirstDayOfMonth(ToDate("10/1/"&Year([Date]))))+Days(1) and
[Date]<=FirstDayOfWeek(LastDayOfMonth(ToDate("12/1/"&Year([Date])))), "Q2 "&Year(AdjustYear([Date], 1)),

//Q3
((Month([Date])<6 and [Date]>=FirstDayOfWeek(LastDayOfMonth(ToDate("12/1/"& Year(AdjustYear([Date],-1)))))+Days(1)) or
[Date]>=FirstDayOfWeek(LastDayOfMonth(ToDate("12/1/"&Year([Date]))))+Days(1)) and
[Date]<=FirstDayOfWeek(LastDayOfMonth(ToDate("3/1/"&Year([Date])))), "Q3 "&Year([Date]),
 
//Q4
Month([Date])<=6 and
[Date]>=FirstDayOfWeek(FirstDayOfMonth(ToDate("4/1/"&Year([Date]))))+Days(1) and
[Date]<=FirstDayOfWeek(LastDayOfMonth(ToDate("7/1/"&Year([Date])))),"Q4 "&Year([Date]))

-Jeff
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Jeff

This is a great solution.  It works great.  Thanks so much for your help.
Photo of Jeff

Jeff

  • 20 Points
Awesome! Glad to help!

-Jeff
Photo of Arshad Khwaja

Arshad Khwaja

  • 3,152 Points 3k badge 2x thumb
Jeff you were kind enough to provide me the solutions.  It is great in terms of a field but problem is that when I am using the Qtr field in a chart it is sorting on the first two letters.  As a result, my sort order appears as under:

Qtr1 2017
Qtr 2 2017
Qtr 2 2018
Qtr 3 2017
Qtr 3 2018

So it is sorting on the Qtr only.

I was hoping to make these appear so that these also get sorted on year so that all quarters appear as under"

Qtr1 2017
Qtr 2 2017
Qtr 3 2018
Qtr 4 2018

If I cannot get what I want, can I please amend the formula so that I could get just the quarters for one FY?   I tried amending the formula but did not have much luck. Or if I could sort the field using another field to make it appear as such.  If nothing else, could I have a text field "FY'  concatenated instead of the year although this is still not the solution for my chart..