Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Technical Questions
Message Icon Topic: Previous Quarter Formula Post Reply Post New Topic
Page  of 2 Next >>
Author Message
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet Topic: Previous Quarter Formula
    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}
IP IP Logged
DBlank
Moderator
Moderator


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

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet 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!!!!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Apr 2018 at 6:37am
are you using this in a select statement?
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet 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.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 13 Apr 2018 at 8:23am
but are the parameter selections used for select criteria?
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet 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!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet 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))))
)

Edited by DBlank - 16 Apr 2018 at 2:54am
IP IP Logged
BoltzGirl
Groupie
Groupie
Avatar

Joined: 22 Jan 2013
Online Status: Offline
Posts: 89
Quote BoltzGirl Replybullet 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

Edited by BoltzGirl - 16 Apr 2018 at 10:20am
Always appreciate the help!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 16 Apr 2018 at 10:24am
can you post the current select criteria formula?
IP IP Logged
Page  of 2 Next >>
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.047 seconds.