Fiscal Year Formula Summing

[wonderif1]

I,m very new to CR and I think I have a basic question. The report that I'm building will calc multiple fiscal years for parts sold from my inventory. I have been successful summing the parts level by fiscal year and now I'm summing them at two other grouping. Example if{@DateFY2001} then Sum ({SA_1_NF.Sales}, {SA_1_NF.Part_Nbr}) I would like to group them at a Customer level and Inventory type level by fiscal year. HELP

 

[Madawc]

You want the fiscal year as the highest level group, right?

You could group using a formula that works out the fiscal year, something like

if month({your.date}) > 4 then "Fiscal " & year({your.date})
else 
if month({your.date}) < 4 then "Fiscal " & (year({your.date})-1)
else
if day({your.date}) > 5 then "Fiscal " & year({your.date})
else 
"Fiscal " & (year({your.date})-1)

That's based on the financial year, which ends 5th April, I think. Anyway, this formula field can then be used for grouping.
Once you have groups, summary totals or running totals should follow easily. Details depend on your version of crystal. For Crystal 10, right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Madawc Williams (East Anglia, UK)

 

[wonderif1]

The Report that I'm trying to generate looks like this.

Customer Name Total Sales FYYTD FY2003 FY2002 FY2001
$$$$ $$$ $$$ $$$ $$$
Ddown to Order Class by Fiscal Year (same Columns)
Ddown to the Parts #Sales Fiscal Year (same Columns)

The Grouping looks like > Part Number then Order Class then Customer Name. The report has the flexibility to veiw highest level summary at the customer name. The formula in my original sample gives me the correct totals. I tried using a formula like this to create the Order class summary unsuccessfully:if{SA_1_NF.Invoice_Date}=Cdate(2001,11,01)to CDate(2002,10,31)and({SA_1_NF.Ord_Class}=["AG","BVF","CA","CP","ER","ES","EU","OM","RM"])then Sum({SA_1_NF.Sales}, {SA_1_NF.Part_Nbr}) and then up to the Customer Name grouping. The sale is recognized at the part number not the customer name. So I grouped the part number sales first. If you can help I would really thank you.

 

[lbass]

After inserting your groups, you should be using one formula per fiscal year. you would modify your formula above to look like:

if {SA_1_NF.Invoice_Date} in Cdate(2001,11,01)to CDate(2002,10,31) and
{SA_1_NF.Ord_Class} in ["AG","BVF","CA","CP","ER","ES","EU","OM","RM"] then {SA_1_NF.Sales}

Then you would place this formula in the detail section, right click on it and insert a summary at all group levels, and at the grand total level, if you like. Ordinarily, if you want a conditional summary, the intent is to apply the condition to detail records and then summarize the results.

If instead you place the summary within the formula itself and then place that formula in the group footer, you would only be getting the group summary for all records in the group (regardless of meeting the criteria) if the last detail record met the conditional criteria, and if it didn't, you would get 0.

-LB

 

[wonderif1]

lbass, It worked, Thanks so much. Maybe someday this will all make sense to me.