conditional running total formula

[edison1135]

I have a report with two groups, the first group is by Manager, and the second is by quarter. The following summary prints in the manager group footer, (so it shows the information for each quarter for each manager.) For the sake of this discussion I've grouped the quarters together for one Marketing Manager. I used running total formulas for most of the fields below.

A B C D E
QTR DRAW EARNED BALANCE PAID TOTAL PAY
Q1 6000 7,500 1,500 1,500 7,500
Q2 6000 5,000 (1,000) 0 6,000
Q3 6000 6,250 250 250 6,250
Q4 6000 5,000 (1,000) 0 6,000

The DRAW is a set target or goal for the manager.
EARNED is the manager’s sales for the quarter.
The BALANCE is EARNED minus the DRAW.
If BALANCE is a positive number, that positive amount is PAID for the quarter, otherwise a “0” is entered.
TOTAL PAY is the sum if DRAW plus PAID.

In the above example, each Quarter is independent of the previous quarter's results. What I want to do is, if the amount in the "BALANCE" column for the previous quarter is negative, add it to the next quarter's BALANCE amount. If it is >= 0, then it wouldn't be added. This sounds simple to do, but I'm having trouble with it. The table would then look like the one below:

A B C D E
QTR DRAW EARNED BALANCE PAID TOTAL PAY
Q1 6000 7,500 1,500 1,500 7,500
Q2 6000 5,000 (1,000) 0 6,000
Q3 6000 6,250 (750) 0 6,000
Q4 6000 5,000 (1,750) 0 6,000

Thanks,

Edison1135

 

[kenhamady]

You will need a formula that declares and increments a running total for the Balance column. See the FAQ on running totals and use a variation of the 3 formula technique. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[edison1135]

Ken,

This is where I'm having trouble. I can't figure out how do refer to a previous quarter and have it add conditionally. I tried using the previous/next, but got an error telling me there is no previous or next. Any suggestions?

Thanks

 

[kenhamady]

You don't need previous/next if you put the formulas in the correct sections. The idea of a variable is that you store a value while it is current, and then use it later. Have you worked with Crystal variables? Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[edison1135]

No, but I'll give it a try. Thanks

 

[kenhamady]

Once you've tried it, post your formulas and how you have used them for more assistance. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[edison1135]

Ken,

Here are examples of the formulas I tried to use, but it still doesn't work. Display is in the manager group header; evaluate is in the details; and initialize is in the manager group footer. I'm a beginner, and I still don't see how I can tell the report to look at a previous quarter's information.

Thanks,

@DisplayAddNegative
WhilePrintingRecords;
NumberVar RunningTotalNegative;
RunningTotalNegative;

@EvaluateAddNegative
WhilePrintingRecords;
NumberVar RunningTotalNegative;
RunningTotalNegative := RunningTotalNegative +
(if {@D}<0 then {@B}-{@A}+{@D}) else ({@B}-{@A};

@InitializeAddNegative
WhilePrintingRecords;
NumberVar RunningTotalNegative;
RunningTotalNegative := 0;

 

[kenhamady]

You don't tell it to look at the previous quarter, it looks at the current quarter and carries the result forward to the next quarter (in the variable).

You have picked a challenging place to start for a beginner, and I haven't worked out everything you need to do, but the following changes will help get you closer:

Put the evaluate on the Quarter's Group Header so that the variable is modified once per quarter. It should be working with the quarter's totals.

The Display should to go on the quarter's group footer, which is the section that will display all of the results.

The initiate should be in the Group header of the manager. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[edison1135]

By placing the formulas where you said, it's starting to look vaguely like it's trying to work, but it's not there yet. The sample I gave you for Evaluate above was incorrect. The &quot;D's&quot; should have been &quot;C's&quot;, so it would look like this:

@EvaluateAddNegative
WhilePrintingRecords;
NumberVar RunningTotalNegative;
RunningTotalNegative := RunningTotalNegative +
(if {@C}<0 then {@B}-{@A}+{@C}) else ({@B}-{@A};

But when I try this, the report doubles the value of &quot;C&quot; for group.

I wonder if there are any vacancies for bus drivers?

 

[kenhamady]

Not sure I understand the problem. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[edison1135]

Let me try again, and please disregard the last confusing message from me.

I entered the formulas where you said to, and used an evaluate formula similar to:

@EvaluateAddNegative
WhilePrintingRecords;
NumberVar RunningTotalNegative;
RunningTotalNegative := RunningTotalNegative +
(if {@C}<0 then {@B}-{@A}+{@C}) else ({@B}-{@A};

But when I try this, instead of adding the value of &quot;D&quot; conditionally, each quarter's &quot;D&quot; value is double the previous quarter's &quot;D&quot; value.

 

[kenhamady]

I think you will need two different variables. One to be the PAID column, and the other to accumulate the leftovers, which I will cal BAL. It might be something like this:

@net:
Earned - Draw

@Eval
(2 variables Paid and Bal)

IF BAL + @NET >= 0
THEN ( PAID := BAL ; BAL := 0)
ELSE BAL:= BAL + @NET
Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training/Consulting and a
Quick Reference Guide to VB/Crystal (including ADO)

 

[edison1135]

Ken, you won't believe it but it finally works! I made a formula that entered the negative amount if the bonus was a negative, otherwise it entered a 0. Then I ended up relocating the running total formulas.

The Evaluate formula ended up looking like this:
//Enters the RunningTotalRollingBalance

WhilePrintingRecords;
NumberVar RunningTotalRollingBalance;
RunningTotalRollingBalance := If ({@RollingBalanceTotal})<0 then ({@RollingBalanceTotal}) Else 0

The @Display and @Evaluate formulas are both in the Quarter Group footer, and @Initialize is in the Manager Group header. After that it was simply a matter of adding the numbers across.

Again, thanks for your patience, and I'm sure I'll be using the forum again.