Trying to calculate sum of amount for Quarter to Date

[michelep]

I am writing a report that will calculate the Month-to-Date, Quarter-to-Date and Year-to-Date expenses for a department. Our fiscal year runs from July 1 to June 30. The database stores the period of the transaction (ie: July would be period 1, August – period 2, etc.) so this can be used in the calculation. I thought I could use the Sum(fld, condFld, cond) to calculate this amount, but when I check it, Crystal says it “cannot calculate running total.” Below is code I wrote to calculate the amount for Quarter to Date.

numbervar CurrentMonth := Month (today);
numbervar CurrentPeriod;
currencyvar QuartertoDate;

//calculate period I am in
if CurrentMonth = 7 then
    CurrentPeriod := 1
else if CurrentMonth = 8 then
    CurrentPeriod := 2  
else if CurrentMonth = 9 then
    CurrentPeriod := 3  
else if CurrentMonth = 10 then
    CurrentPeriod := 4
else if CurrentMonth = 11 then
    CurrentPeriod := 5
else if CurrentMonth = 12 then
    CurrentPeriod := 6
else if CurrentMonth = 1 then
    CurrentPeriod := 7
else if CurrentMonth = 2 then
    CurrentPeriod := 8
else if CurrentMonth = 3 then
    CurrentPeriod := 9
else if CurrentMonth = 4 then
    CurrentPeriod := 10
else if CurrentMonth = 5 then
    CurrentPeriod := 11
else if CurrentMonth = 6 then
    CurrentPeriod := 12;

//calculate what month in the quarter I am in
if (CurrentMonth mod 3) = 1 then
//QTD is only current month
    QuartertoDate := Sum ({tfgld106.amnt},{tfgld106.rprd}, CurrentPeriod);

I am fairly new to Crystal and would appreciate any advice on using the Sum function or suggestions for a different way to calculate this.
Thanks,

michele

 

[synapsevampire]

Rather than posting what doesn't work, consider posting the intent (which you did pretrty well), example data and expected output.

The way I handle this is to always create a Periods table which has the actual data, and a fiscal date, as well as a fiscal month, year, etc.

Thsi is standard fare in Data Warehousing, and will simplify many reporting/querying tasks.

Doing this in Crystal is a pain, this will help with one of your formulas:

@FiscalMonth
if CurrentMonth > 6 then
CurrentPeriod := CurrentMonth-(CurrentMonth-1)
else
Currentperiod := CurrentMonth+6

Now please demonstrate how you would like to see the output.

-k

 

[michelep]

The period calculation works already, but I am having trouble calculating the Quarter to Date sum. I need the output to calculate as follows:

July Quarter to Date July Year to Date July
1,000.00 1,000.00 1,000.00

August Quarter to Date August Year to Date August
1,000.00 2,000.00 2,000.00

September Quarter to Date September Year to Date September
1,000.00 3,000.00 3,000.00

October Quarter to Date October Year to Date October
1,000.00 1,000.00 4,000.00

thanks michele

 

[synapsevampire]

As I suspected, you're intending to display running totals.

You can create running totals to do this, just place something akin to the following in each Running Total:

I'm having some odd values returned by MOD right now, so I'll use use a formula to group by:

If @CurrentPeriod < 4 then
&quot;Q1&quot;
else
If @CurrentPeriod > 3 and @CurrentPeriod < 7 then
&quot;Q2&quot;
else
... you get the idea

Now create a running total, as in:

Add the field to sum, select sum, reset at the @currentperiod group.

Should work OK.

-k

 

[michelep]

Crystal is not letting me create a sum within the group that I already have. I keep getting the message &quot;The summary / running total field could not be created.&quot; The report already filters the report by department and groups the section by general ledger account. I need to sum the amount spent monthly, quarter to date and year to date within that grouping. Is this possible?

michele