Previous Four Rolling Quarters 2

[jdaily]

Hi all!

I'm using CR10 with an Oracle db.

I'm havning a problem figuring out how to get this done. I am trying to pull a small amount of data that looks in the previous 4 rolling quarters, excluding the current quarter. I only need the previous 4 full quarters data, no partial quarters. The field that I would use is a standard date field to determine the quarters.

I guess what it would need to do is start at the end of the previous quarter and then go back 12 months.

I would like to use this formula in the selection criteria if I could.

Any ideas?

Thanks in advance!

 

[Madawc]

You can get quarters based on the current date. Try

DateDiff ("q", {data.date1}, currentdate ) in [1 to 4]

If you don't get the result you expect, move the DateDiff part to a formula and display it against the record details.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jdaily]

Madawc thank you for your help!

I tried it but it gave me all of the previous quarters. I only need the previous 4 full quarters.

Any suggestions?

Thanks,

John

 

[synapsevampire]

I would guess that you're being lazy in posting your requirements, and that you actually need to display each quarters data individually or summarized, not that you only need it for the record selection.

Create a formula of:

if month(currentdate) in 1 to 3 then
{table.date} >= cdate(year(currentdate)-1,1,1)
and
{table.date} <= cdate(year(currentdate)-1,12,31)
else
if month(currentdate) in 4 to 6 then
{table.date} >= cdate(year(currentdate)-1,4,1)
and
{table.date} <= cdate(year(currentdate),3,31)
else
if month(currentdate) in 7 to 9 then
{table.date} >= cdate(year(currentdate)-1,4,1)
and
{table.date} <= cdate(year(currentdate),6,30)
else
if month(currentdate) in 10 to 12 then
{table.date} >= cdate(year(currentdate)-1,7,1)
and
{table.date} <= cdate(year(currentdate),9,30)

Note than each of the above can be used as the criteria for aggregates per quarter with a slight tweak:

if month(currentdate) in 1 to 3 then
{table.date} >= cdate(year(currentdate)-1,1,1)
and
{table.date} <= cdate(year(currentdate)-1,3,31)

...etc...

-k

 

[jdaily]

You are probably right in that I wasn't very clear in my requirements.

I have a crosstab that has {clm.quarter} as a column and {prod.product} as the row. Then I have a total formula that gives the total dollar amount based.

The only problem is that I need the data in period that were paid, which is identified by {stlmnt.stlmnt_date_sent} date.

I wanted to hard code it in the record selection criteria so that the user doesn't have to do anything to the report. I guess what I could do then is take the date and covert it to a quarter and have the user select that as a paramter, if possible.

 

[synapsevampire]

Why not just place the above formula in your record selection?

-k

 

[jdaily]

synapse you're awesome!

Thank you for your help!

The formula worked like a charm.

I had created a formula that took the {date_sent} and created a quarter out of it that gave the quarter and year concatenated.

I took your formula and put it in my record selection and it gave me the exact quarters I needed.

Thank you!

john