Previous Quarter Formula

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}

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)))

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!!!!

are you using this in a select statement?

No we created this as a begin and end date formula, so they can choose these options through a parameter.

but are the parameter selections used for select criteria?

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}

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))))
)

Edited by DBlank - 16 Apr 2018 at 2:54am

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

Edited by BoltzGirl - 16 Apr 2018 at 10:20am

can you post the current select criteria formula?

{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'])

{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})
)

Edited by DBlank - 16 Apr 2018 at 10:38am

That did work for us, thank you very much!