Maximum Running Total

[halaville]

Does anyone know how to get a maximum running total based on another field? For example, a running total of quantity purchased during the year up to ending inventory quantity.

QTY PURCH Running TOtal Ending Inv
80 80 200
20 100
50 150
60 210 (but I want this to be max 200=Ending Inv)

 

[lbass]

Use a formula like this:

if {#rt} < 200 then
{#rt} else
200

If you have a field that represents the maximum, then substitute that for the 200.

-LB

 

[halaville]

Thanks Ibass. I will try this tomorrow. How can I then have the next record after the maximum amount return 0?

QTY PURCH Running TOtal Ending Inv
80 80 200
20 100
50 150
60 210 (but I want this to be max 200=Ending Inv)
100 0 because the previous record already met the maximum amount of the field or 200

 

[lbass]

Try changing the formula to:

whileprintingrecords;
numbervar cnt;
numbervar result;
if {#rt} < 200 then
result := {#rt} else
if {#rt} >= 200 then (
cnt := cnt + 1;
if cnt = 1 then
result := 200 else
result := 0
);
result

-LB

 

[halaville]

Thank you for your input so much.
This formula makes my last 210 result as 0 and thereafter.
However, I want my 210 to be 200 and the rest of the running total be 0. More important I am after the Qty Purch upto Inv column to run inventory costing based on actual purchase.

Like this:
QTY PURCH Running TOtal Ending Inv Qty Purch upto Inv
80 80 200 80 20 100 20
50 150 50
60 210 50
100 0 because the previous record already met the maximum amount of the field or 200

 

[lbass]

I don't have a clue what you mean by:

More important I am after the Qty Purch upto Inv column to run inventory costing based on actual purchase.

Is this a new requirement?

-LB

 

[halaville]

I guess, and I cant get my columns to lineup
QTY Running Ending Qty Purch
PURCH TOtal Inv upto Inv
80 80 200 80
20 100 200 20
50 150 200 50
60 200* 200 50
100 0** 200 0

*210 should be replaced by 200 because it exceed Ending Inv.
**0 because the previous record already met the maximum amount of the field or 200

 

[lbass]

My formula works for me when I use it in a detail section. Are your numbers group summaries instead? If so, are they inserted summaries or formulas? If formulas, please show the content.

Also, you didn't clarify what you meant by the line I quoted.

-LB

 

[halaville]

Yes, it is new. It is the criteria that I thought I can achieve by figuring out the max running balance.

The report is grouped by item keys and displays every purchase showing most recent purchase first with quantity and price.

Here are my fields and formulas in the detail section:
Qty Purch is a field from a table.
Running total is a running total of Qty Purch in the detail and resets at the next group name.
Ending Bal is actually a "mainformula" in the header & detail of the main report based on subreport.
Qty purchased up to inventory amount is the field I am trying to get so I can value inventory based on FIFO. So, I want Crystal to breakdown the ending inventory amount based on last purch and if it is less than ending inventory then go to the next line until the accumulated sum of Qty Purch is up to Ending Inventory. The table above shows the results I want.

 

[lbass]

So am I seeing details in your sample data? Because my formula works at the detail level. Please show me the exact formula you used in trying to implement my suggestion--not in words. Please just copy and paste.

Once you have that working, are there any remaining issues?

-LB

 

[halaville]

Yes, all this is in detail. Detail of each purchase needs to stop after my ending inventory quantity is met.

This formula brings the results I want but then keeps listing detail with the maximum @MainFormula (ending inv quantity) thru out the detail. I don't want to show details after my ending inv quantity is met:

IF {#Qty Purchased} <{@MainFormula} THEN {#Qty Purchased} ELSE {@MainFormula}

This is the second formula and it gives me results up to the last record where it should show my ending inventory quantity, instead it shows 0.

whileprintingrecords;
numbervar cnt;
numbervar result;
if {@Qty on Hand} < {@MainFormula} then result := {@Qty on Hand}
else if {@Qty on Hand} >= {@MainFormula} then (cnt := cnt + 1;
if cnt = 1 then result := {@MainFormula} else result := 0)
;result

Once again, thank you for your help!

 

[lbass]

Please show the content of {@Qty on Hand} and {@Main Formula} and of any nested formulas.

I also don't know the name of the formula you showed or what you want it to do.

-LB

 

[halaville]

{@QTY ON HAND} is my running balance up to my ending inventory quantity which keeps repeating after the max is reached.

IF {#Qty Purchased} <{@MainFormula} THEN {#Qty Purchased} ELSE {@MainFormula}
------------------
{@MAINFORMULA} is my ending inventory quantity which pulls from my subreport

//@MainFormula
//Returns the value that was stored
//in the shared number variable called
//myTotal in the subreport

WhilePrintingRecords;
Shared NumberVar myTotal;
myTotal
-----------
{@MAINFORMULA} is based on total from subreport {@SUBFORMULA}:

//@SubFormula
//Stores the grand total of the
//Sum ({@Subtotal Wt}, {INMAST.Itemkey}) field
//in a NUMBER variable called 'myTotal'

WhilePrintingRecords;
Shared NumberVar myTotal := IF ISNULL
(Sum ({@Subtotal Wt}, {INMAST.Itemkey})) THEN 0 ELSE Sum ({@Subtotal Wt}, {INMAST.Itemkey})
_________
{@Subtotal Wt} which is a formula in the subreport that totals up my inventory ending balance

{MANUAL_COUNT_RM.# OF CONTAINERS}*{MANUAL_COUNT_RM.Weight}


It is nested alright!

 

[lbass]

Where is the subreport located? If it is in the detail section, move it to detail_a and then move all other formulas to detail_b. I think the shared variable is being read one record late because of the location of the sub.

-LB

 

[halaville]

The subreport is currently in the group header. The {@mainformula} is also located in the detail section.

I did your recommendation above and it gave me the same results where the last entry is 0 and it should be the ending inventry amount.

 

[lbass]

You need to also add a reset formula in the group header:

whileprintingrecords;
numbervar cnt;
if not inrepeatedgroupheader then
cnt := 0;

And you also need a reset formula in the report header of the subreport:

whileprintingrecords;
shared numbervar myTotal := 0;

I recreated your report (again) and this did work.

-LB