CR8.5-SQL7: Need Help w/ Formula-current Quarter Based On Current Date 1

[Drakhan]

How would I go about creating a forumla to calculate sales figures based on the current quarter given the current date? (i.e., today). Here is what I would like to create:

Name Current Quarter
Mike $7,000,000
Josh $5,000,000
Joe $1,000,000
Lester $500,000

The dollar figures are Current Quarter booked sales. The sales figures ("Current Quarter Column) are calculated by adding all of the sales that are booked for the current quarter.

I use the 'Select Expert' tool to narrow down the selection of the sales figures to only the booked amount. However, all I seem to get is a calculation of data from the beginning of the database to the present. What I would like to do is create a formula that will just show the 'Current Quarters' total booked sales. I am using CR 8.5 on SQL 7.0.

Field Names:

{sales.rep} = sales rep name
{sales.booked} = the sales figures for 'sold' deals

I have tried to mess around a bit with the CurrentDate function to get today's date and have tried using conditional statements based upon the CurrentDate's finding, but to no avail. Does anyone have a formula for calculating the current quarter based on today's date?? Thanks in advance!

 

[dgillz]

Create a formula to find the month where the quarter starts, then take it a step farther in your record selection formula:

@month
if month(CurrentDate) in 1 to 3 then 1 else
if month(CurrentDate) in 4 to 6 then 4 else
if month(CurrentDate) in 7 to 9 then 7 else 10

Record selection formula:

{datefield} in Date(Year(CurrentDate),{@month},1) to CurrentDate

Let me know if you have any questions


Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[Drakhan]

You are the MAN! Thank you so much! I think this might work!

 

[Drakhan]

dgillz,

It works so far, I guess, but it is a booleen statement, returning 'True' for the correct dates, however, how do I get them to calculate on those trues? I would like to have the Current Quarter column calculate a sum based on the 'True'...this is probably a very simple procedure, but I am rather new Crystal Reports. Any help would be greatly appreciated. Thanks again!!

 

[dgillz]

If {@CurrentQuarterCheck}= true then {FieldName} else 0.

Then sum this formula as desired. Software Training and Support for Macola, Crystal Reports and Goldmine
251-621-8972
dgilsdorf@mchsi.com

 

[Drakhan]

Thanks dgillz! Great job!! It worked perfectly!