Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Fiscal year help Post Reply Post New Topic
Author Message
Beso90
Groupie
Groupie
Avatar

Joined: 12 Jun 2014
Location: United States
Online Status: Offline
Posts: 48
Quote Beso90 Replybullet Topic: Fiscal year help
    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!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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')
IP IP Logged
Beso90
Groupie
Groupie
Avatar

Joined: 12 Jun 2014
Location: United States
Online Status: Offline
Posts: 48
Quote Beso90 Replybullet 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.

IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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')
IP IP Logged
Beso90
Groupie
Groupie
Avatar

Joined: 12 Jun 2014
Location: United States
Online Status: Offline
Posts: 48
Quote Beso90 Replybullet Posted: 21 Aug 2018 at 6:45am
It worked! Thank you so much!!!!
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.