Previous Quarter Formula
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=22574
Printed Date: 01 May 2024 at 12:39pm
Topic: Previous Quarter Formula
Posted By: BoltzGirl
Subject: Previous Quarter Formula
Date Posted: 11 Apr 2018 at 7:09am
I have a formula that is supposed to pull the previous quarter when using the 01/01/1925 in our parameters. Today we got this far and it's showing dates 10/01/2017 to 03/31/2018....how do I tweak this to get it to show 01/01/2018 to 03/31/2018?
BEG DATE:
dateVar EndMonth := Date(Year(DataDate),Month(DataDate),01)-1;
If {?Beg} = Date(1800,01,01) Then
Date(Year(EndMonth),Month(EndMonth),01)
Else
If {?Beg} = Date(1925,01,01) Then
Switch (Month(EndMonth) in 1 to 3,Date(Year(DataDate)-1,10,01),
Month(EndMonth) in 4 to 6,Date(Year(DataDate),01,01),
Month(EndMonth) in 7 to 9,Date(Year(DataDate),04,01),
Month(EndMonth) in 10 to 12,Date(Year(DataDate),07,01))
Else
{?Beg}
END DATE:
If {?End} = Date(1800,01,01) Then
Date(Year(DataDate),Month(DataDate),01)-1
Else
If {?End} = Date(1925,01,01) Then
Switch (Month(DataDate) in 1 to 3,Date(Year(DataDate)-1,12,31),
Month(DataDate) in 4 to 6,Date(Year(DataDate),03,31),
Month(DataDate) in 7 to 9,Date(Year(DataDate),06,30),
Month(DataDate) in 10 to 12,Date(Year(DataDate),09,30))
Else
{?End}
|
Replies:
Posted By: DBlank
Date Posted: 12 Apr 2018 at 5:00am
maybe these?
last quarter start
dateadd('q',DATEDIFF('q',DATE(1900,1,1),today)-1,DATE(1900,1,1))
last quarter end
DATEADD('d',-1,dateadd('q',DATEDIFF('q',DATE(1900,1,1),today),DATE(1900,1,1)))
|
Posted By: BoltzGirl
Date Posted: 13 Apr 2018 at 6:34am
So I have had to add other code to this, so that we can pull the report by other date parameters and this is my full code.
dateVar EndMonth := Date(Year(DataDate),Month(DataDate),01)-1;
If {?Denied Date From} = Date(1800,01,01) Then
(Date(Year(EndMonth),Month(EndMonth),01))
else
if {?Denied Date From} = Date(1850,01,01) then
minimum(lastfullweek)
else
if {?Denied Date From} = Date(1900,01,01) then currentdate -1
else
If {?Denied Date From} = Date(1925,01,01) Then
dateadd('q',DATEDIFF('q',DATE(1900,1,1),today)-1,DATE(1900,1,1))
{?Denied Date From}
Right now with adding the line you suggested, I get nothing else but the quarter now, the others don't work. So I am guessing I have () in the wrong places...?
The other thing that your line added to the report is the time, so it shows like this.
1/1/2018 12:00AM - 03/31/2018 12:00AM
I only want the dates and not the time. I appreciate your help SO very much!! TIA if you have any suggestions on how to add your line into my full formula and removing the time.
Thank you again!!!!
|
Posted By: DBlank
Date Posted: 13 Apr 2018 at 6:37am
|
are you using this in a select statement?
|
Posted By: BoltzGirl
Date Posted: 13 Apr 2018 at 7:01am
|
No we created this as a begin and end date formula, so they can choose these options through a parameter.
|
Posted By: DBlank
Date Posted: 13 Apr 2018 at 8:23am
|
but are the parameter selections used for select criteria?
|
Posted By: BoltzGirl
Date Posted: 13 Apr 2018 at 10:33am
The parameters yes will pull back date periods based on this formula;
If they choose 01/01/1800 - it will pull the prev month
If they choose 01/01/1850 - it will pull the prev week (sun-sat)
If they choose 01/01/1900 - it will pull the prev day
If they choose 01/01/1925 - it will pull the prev quarter
We use these commonly on our reports for the end users to have more flexibility in scheduling them for these specific periods of time. The code that we use for the first 3 prompts above works fine. It's when we went to add a 4th option to pull the previous month, where it breaks and gives me 10/01/2018-12/31/2018.
dateVar EndMonth := Date(Year(DataDate),Month(DataDate),01)-1;
If {?Denied Date From} = Date(1800,01,01) Then
(Date(Year(EndMonth),Month(EndMonth),01))
else
if {?Denied Date From} = Date(1850,01,01) then
minimum(lastfullweek)
else
if {?Denied Date From} = Date(1900,01,01) then currentdate -1
else
******THIS IS WHAT IS PULLING START MONTH OF OCTOBER, RATHER THAN JANUARY FOR PREV QUARTER*******
If {?Denied Date From} = Date(1925,01,01) Then
Switch (Month(EndMonth) in 1 to 3,Date(Year(DataDate)-1,10,01),
Month(EndMonth) in 4 to 6,Date(Year(DataDate),01,01),
Month(EndMonth) in 7 to 9,Date(Year(DataDate),04,01),
Month(EndMonth) in 10 to 12,Date(Year(DataDate),07,01))
*************
Else
{?Denied Date From}
-------------
Always appreciate the help!
|
Posted By: DBlank
Date Posted: 16 Apr 2018 at 2:53am
you can just move this all to the select criteria.
I assume you are pulling in some sort of sales or transaction date.
the portion of your select will look something like this
(
({?Denied Date From}= DATE(1800,1,1) and {table.salesdate} in LastFullMonth)
OR
({?Denied Date From}= DATE(1850,1,1) and {table.salesdate} in LastFullWeek)
OR
({?Denied Date From}= DATE(1900,1,1) and {table.salesdate}=DATEADD('d',-1,today))
OR
({?Denied Date From}= DATE(1925,1,1) and {table.salesdate} in dateadd('q',DATEDIFF('q',DATE(1900,1,1),today)-1,DATE(1900,1,1)) to DATEADD('d',-1,dateadd('q',DATEDIFF('q',DATE(1900,1,1),today),DATE(1900,1,1))))
)
|
Posted By: BoltzGirl
Date Posted: 16 Apr 2018 at 10:13am
I tried that suggestion and now I get a 'This requires a date time' for my Beg/End date parameters. They are already date fields, don't need the time.
:-(
I messed around with my original formula below and can see that it will pull the Jan-Mar (1st Quarter) when I remove the -01 ** I added the ** by that line to show you more specifically and in red but that makes me think it will break when a new year comes....
dateVar EndMonth := Date(Year(DataDate),Month(DataDate),01)-1;
If {?Denied Date From} = Date(1800,01,01) Then
(Date(Year(EndMonth),Month(EndMonth),01))
else
if {?Denied Date From} = Date(1850,01,01) then
minimum(lastfullweek)
else
if {?Denied Date From} = Date(1900,01,01) then currentdate -1
else
If {?Denied Date From} = Date(1925,01,01) Then
Switch (Month(EndMonth) in 1 to 3,Date(Year(DataDate),01,01),
Month(EndMonth) in 4 to 6,Date(Year(DataDate)}**-01,01,01),
Month(EndMonth) in 7 to 9,Date(Year(DataDate),04,01),
Month(EndMonth) in 10 to 12,Date(Year(DataDate),07,01))
Else
{?Denied Date From
-------------
Always appreciate the help!
|
Posted By: DBlank
Date Posted: 16 Apr 2018 at 10:24am
|
can you post the current select criteria formula?
|
Posted By: BoltzGirl
Date Posted: 16 Apr 2018 at 10:25am
{XRPT_AUTH_MASTER_VW.AUTH_STATUS} = "DN" and
{XRPT_AUTH_MASTER_VW.MEH_LINE_OF_BUSINESS} like {?Line of Business} and
{XRPT_AUTH_MASTER_VW.HEALTH_PLAN_NAME} like {?Health Plan} and
{XRPT_AUTH_MASTER_VW.DENIED_DATE} >= {@Beg date} and
{XRPT_AUTH_MASTER_VW.DENIED_DATE} <= {@End date} and
not({XRPT_AUTH_MASTER_VW.AUTH_TYPE} in ['DIS','CCM','CMG','CVG','DUP','TRK'])
-------------
Always appreciate the help!
|
Posted By: DBlank
Date Posted: 16 Apr 2018 at 10:38am
{XRPT_AUTH_MASTER_VW.AUTH_STATUS} = "DN" and
{XRPT_AUTH_MASTER_VW.MEH_LINE_OF_BUSINESS} like {?Line of Business} and
{XRPT_AUTH_MASTER_VW.HEALTH_PLAN_NAME} like {?Health Plan} and
(not({XRPT_AUTH_MASTER_VW.AUTH_TYPE} in ['DIS','CCM','CMG','CVG','DUP','TRK']))
AND
(
({?Denied Date From}= DATE(1800,1,1) and {XRPT_AUTH_MASTER_VW.DENIED_DATE} in LastFullMonth)
OR
({?Denied Date From}= DATE(1850,1,1) and {XRPT_AUTH_MASTER_VW.DENIED_DATE} in LastFullWeek)
OR
({?Denied Date From}= DATE(1900,1,1) and {XRPT_AUTH_MASTER_VW.DENIED_DATE}=DATEADD('d',-1,today))
OR
({?Denied Date From}= DATE(1925,1,1) and {XRPT_AUTH_MASTER_VW.DENIED_DATE} in dateadd('q',DATEDIFF('q',DATE(1900,1,1),today)-1,DATE(1900,1,1)) to DATEADD('d',-1,dateadd('q',DATEDIFF('q',DATE(1900,1,1),today),DATE(1900,1,1))))
OR
({?Denied Date From}> DATE(1925,1,1) and {XRPT_AUTH_MASTER_VW.DENIED_DATE} in {?Denied Date From} to {?Denied Date To})
)
|
Posted By: BoltzGirl
Date Posted: 17 Apr 2018 at 5:05am
That did work for us, thank you very much!
-------------
Always appreciate the help!
|
|