How to write formulas that sum the first 3 records of a group

[Tarq]

Greetings.

In my report, I'm grouping by Item_Code. For each group 12 rows are comming back (1 per month) in decending order.

I need to have 4 forumlas that do the following...

'// First
([Sum of first 3 months shipments] * 4)
/
(Sum of 3 months inventory)/3


'// Second
([Sum of first 3 months shipments] * 4)
/
(Sum of 6 months inventory)/6

'// Third
([Sum of first 3 months shipments] * 4)
/
(Sum of 9 months inventory)/9

'// Fourth
([Sum of first 3 months shipments] * 4)
/
(Sum of 12 months inventory)/12

The "numerator" for each group remains the same for each column

I made it work for 1 group using a function in the detail:
'//
WhilePrintingRecords;

Global NumberVar ShipmentSum;
NumberVar i:=0;

if RecordNumber < 4 then
ShipmentSum:= ShipmentSum + {TurnsOnly_ttx.MTD_Shipments};
'//

and in the group footer...
'//
Global NumberVar InventorySum3;

If RecordNumber < 4 then
InventorySum3 := InventorySum3 + {TurnsOnly_ttx.MTD_OnHand};
'//

Umm... this worked for 1 group, but not for the others... I'm a VB guru by nature, and I don't have a lot of experience with Crystal. Please Help

-T

 

[alley211]

Hi,

It's Friday so maybe I'm making this way more complicated than I need to.

My thought is to create:

1: a variable to count 1 to 12, reset each group
2: a variable to calculate the inventory amount, reset each group
3: another formula that states something like this:

select {@count}
case 3:
global numbervar three := {@amount}
case 6:
global numbervar six := {@amount}
case 9:
global numbervar nine := {@amount}
case 12:
global numbervar twelve:={@amount}

(Even though it appears redundant, it is grabbing the correct amount field)

4: variable to create the 1st 3months shipments, reset each group

Then you can use the formulas in the final 4 formulas that you discussed, tweaking to use the variables instead.

Again, its Friday, I may be getting way more complicated than I need to, but maybe this will lead you in the right direction.


alley

 

[dgillz]

I have never tried this but how about inserting another summary field by item_code, and making it a % of a grand total of the dollars in question. This will of course give you 12 percentages, which you do not want.

To get rid of the unwanted percentages (here's the part I have not tried) use conditional suppression if the group number (group month number?) is not perfectly divible by 3.

Let me know if you can get this to work, or feel free to send me a report via email and I will take a look at it.

dgilsdorf@altavista.com
Software Support for Sage Mas90, Macola, Crystal Reports, Goldmine and MS Office

 

[Ngolem]

Ok...let me see if I got this straight...

1. each detail line includes the following fields for a given month:
for inventory values: {TurnsOnly_ttx.MTD_OnHand}
for shipment : {TurnsOnly_ttx.MTD_Shipments}

2. your detail line are ordered by Month descending (really this doesn't matter)...the fact is that you want the shipping totals for the first 3 records (months)of each group....correct and divide that by the various inventory totals.

3. and you want the result to be placed in the footer for a given item Group.

Using RecordNumber as you have shown is not good since RecordNumber is cumulative and does not reset after each group.

I would use the a &quot;Three formula&quot; approach to this problem.

First formula
=============

@Initialization (placed in the item group header and
suppressed)

******************* Formula start ******************

whileprintingrecords;
//this stops unwanted resets
if not inRepeatedGroupHeader then
( numbervar ShipmentSum := 0;
numbervar InventorySum_3month := 0;
numbervar InventorySum_6month := 0;
numbervar InventorySum_9month := 0;
numbervar InventorySum_12month := 0;
numbervar counterflag := 0;
);

******************* Formula end ******************

Second Formula
==============

@Calculation_of_sums (suppressed in the detail section)

******************* Formula start ******************

whileprintingrecords;
numbervar ShipmentSum ;
numbervar InventorySum_3month ;
numbervar InventorySum_6month ;
numbervar InventorySum_9month ;
numbervar InventorySum_12month ;
numbervar counterflag;

counterflag := counterflag + 1;

if counterflag <= 3 then
(
ShipmentSum := ShipmentSum +
{TurnsOnly_ttx.MTD_Shipments};
InventorySum_3month := InventorySum_3month +
{TurnsOnly_ttx.MTD_OnHand};
);

if counterflag <= 6 then
InventorySum_6month := InventorySum_6month +
{TurnsOnly_ttx.MTD_OnHand};
if counterflag <= 9 then
InventorySum_9month := InventorySum_9month +
{TurnsOnly_ttx.MTD_OnHand};
if counterflag <= 12 then
InventorySum_12month := InventorySum_12month +
{TurnsOnly_ttx.MTD_OnHand};

******************* Formula end ******************

Third Formula
==============

Actually this is only one of 4 display formulas to be placed in the footer line of the ITEM Group

@Display_First (placed in Item Group footer not suppressed)

******************* Formula start ******************
whileprintingrecords;
numbervar ShipmentSum ;
numbervar InventorySum_3month ;

//avoid division by zero error
if InventorySum_3month <> 0 then
ShipmentSum * 4 / InventorySum_3month / 3
else
0;

******************* Formula end ******************

Three other similar &quot;Display&quot; formulas for the other sums are required.

If I understand your problem correctly that should work

regards Jim