Fiscal year help
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22657
Printed Date: 03 May 2024 at 10:25pm
Topic: Fiscal year help
Posted By: Beso90
Subject: Fiscal year help
Date Posted: 10 Aug 2018 at 7:18am
|
Hello,
I have a date field called {RELEASES.MUST_SHIP_DATE} I need a formula to tell me which Quarter and which year does it fall under. our fiscal year start to end are July 1st to June 31st. So now that we are in August of 2018, we are in Q1 2019. August of 2017 was Q1 2018. and so on... How do I go about making this? I created a formula to display the month name (called {@Month Name}) and then created an "if" formula for the following: if {@Month Name} in ["January", "February", "March"] then "Q3" else if {@Month Name} in ["April", "May", "June"] then "Q4" else if {@Month Name} in ["July", "August", "September"] then "Q1" else "Q2" I need a formula to display the fiscal year of the must ship date field and then I can just make a formula to combine the Quarter + " " + Year and I'll be golden Thanks!! |
Replies:
Posted By: DBlank
Date Posted: 10 Aug 2018 at 9:43am
|
Maybe this?
'Q' + totext(DATEPART('q',dateadd('m',-6,{RELEASES.MUST_SHIP_DATE})),0,'') + totext(dateadd('m',-6,{RELEASES.MUST_SHIP_DATE}),'yyyy') |
Posted By: Beso90
Date Posted: 10 Aug 2018 at 10:04am
|
Hello,
Didn't quite work yet. Please see below image. Column "Must Ship Date" is the date field I'm basing the formula off of. And column "Quarter" is where the formula is. 
|
Posted By: DBlank
Date Posted: 20 Aug 2018 at 9:42am
|
Change it from -6 to 6
'Q' + totext(DATEPART('q',dateadd('m',6,{RELEASES.MUST_SHIP_DATE})),0,'') + totext(dateadd('m',6,{RELEASES.MUST_SHIP_DATE}),'yyyy') |
Posted By: Beso90
Date Posted: 21 Aug 2018 at 6:45am
| It worked! Thank you so much!!!! |