Subreport for last 4 quarters 1

[mmarino]

Hi there, I'm trying to create a subreport and I'm not getting the right data.
I have a table with the following fields:
date, numberA, $amount
I need to show the sum of numberA and the sum of $amount per quarter for the last 4 quarters, so I would end up with four lines. I tried creating a beginDate and endDate for each quarter, but I don't know how to tell my formula to only get the records in between those dates. I can do the SQL statement for it, but I don't know how to translate that to Crystal.
The output should look like this:
Jan-Mar 2005 10 1000
Oct-Dec 2004 25 6100
Jul-Sep 2004 3 550
Apr-Jun 2004 74 8900
Thanks for your help!

mm

 

[ShawnPDuffy]

What version of CR are you using?

 

[lbass]

You can create a formula like the following:

if {table.date} in date(2004,04,01) to date(2004,06,30) then "Apr - Jun 2004" else
if {table.date} in date(2004,07,01) to date(2004,09,30) then "Jul - Sep 2004" else
if {table.date} in date(2004,10,01) to date(2004,12,31) then "Oct - Dec 2004" else
if {table.date} in date(2005,01,01) to date(2004,03,31) then "Jan - Mar 2004"

Then insert a group on this formula. You can then right click on numberA and $amount in the detail section and insert a summary. You can then drag the group name into the group footer and suppress the detail section.

-LB

 

[mmarino]

I'm using CR 11. Thanks for the help LB, I understand what I need to do a little bit better now.
I need to make it more dynamic, so in a couple of months when we switch quarters I don't have to change anything in the report, so I can't use specific dates like you suggested.
I found this formula for the beginning of the last quarter on this site:

If (Month(CurrentDate) < 4) Then
DateTime(Year(CurrentDate)-1,10,1,0,0,0)
Else
DateTime(Year(CurrentDate),3*Truncate((Month(CurrentDate)+2)/3)-5,1,0,0,0)[/color red]

and a similar one for the end of the quarter date. But that's just for the last quarter, how do I get the other 3? and how do I do the comparing?
Thanks a bunch for the help

mm

 

[Kebabmeister]

If Month(YourDate)>=1 and Month(YourDate) <=3 Then
"Jan - Mar " + ToText(Year(YourDate),0,"")
Else
If Month(YourDate)>=4 and Month(YourDate) <=6 Then
"Apr - Jun " + ToText(Year(YourDate),0,"")
Else
If Month(YourDate)>=7 and Month(YourDate) <=9 Then
"Jul - Sep " + ToText(Year(YourDate),0,"")
Else
If Month(YourDate)>=10 and Month(YourDate) <=12 Then
"Oct - Dec " + ToText(Year(YourDate),0,"")

 

[mmarino]

Thanks Kebabmeister! That does the trick
One more small thing. I need to omit the first group, for example, it is April and we're in the second quarter, but I need to keep it off the report. I only need the last quarter and the previous 3. Is there a way to do that by using top N?
Thanks,

mm

 

[Kebabmeister]

Am I understanding you correctly? You need to only show complete quarters?

 

[mmarino]

Yes only the previous 4 complete quarters, but I have data in the database for the last 18 months up to today, so I need to filter out the data that doesn't belong.

mm

 

[Kebabmeister]

Place this in the record selection.

YourDate >= (Date(Year(CurrentDate),(Truncate(Month(CurrentDate)/3,0)*3)+1,1) - 365) and
YourDate<= Date(Year(CurrentDate),(Truncate(Month(CurrentDate)/3,0)*3)+1,1) -1

 

[mmarino]

You're a genius! Thank you so much for all the help

mm