Help Crystal 8.5 formula problems

[Asha2004]

I have a report that I am developing. I have a couple of problems. I am trying to do a formula on purchased capacity. Here is my table layout. I have created a group on name: Here is what my report looks like. There are around 62 names. Each group has the same dates.

Group 1: Name:Angelica

Details:
Date BaseCapacity adjustedloadings Purchase capac.
2/15/2003 311 95
4/15/2003 311 0
6/15/2003 311 85
8/15/2003 311 0
10/15/2003 311 100
12/15/2003 311 0

The formula for purchased capacity should be something like this. This is Psuedo....

Previous (BaseCapacity}) + (Previous(AdjustedLoadings)/18)

The problem with this is first of all, I need it to reset for each group to get the basecapacity. Second, it is not keeping the values. I can only get it to calculate for one field. I didn't know if I need the whileprintingrecords. I tried that, and it didn't seem to do anything.

Please help

 

[HowardHammerman]

What do you want to happen when you are processing the first record of a group? Let's assume that you want it to be zero. If so, try something like this:

whileprintingrecords; // never hurts
if {table.groupvalue}=previous({table.groupvalue}) then 0
else
Previous ({table.BaseCapacity}) + Previous({table.AdjustedLoadings})/18

Note that I put the parentheses in different places than you did.

I hope this helps,

cheers!


Howard Hammerman,
Crystal Training and Crystal Material
On-site and public classes
Low-cost telephone/email support
FREE independent Crystal newsletter
howard@hammerman.com

http://www.hammerman.com

800-783-2269

 

[Asha2004]

This is what I put into the PurchasedCapacity formula

whileprintingrecords; // never hurts
if GroupName ({Industrial_Step_Study_All.Name}) = previous(GroupName ({Industrial_Step_Study_All.Name}))
then {Industrial_Step_Study_All.BODCapacity}
else
Previous ({Industrial_Step_Study_All.BODCapacity}) + Previous({@BOD Adjusted Loadings (LBS per Day)})/18


I get an error message saying: This field has no previous or next value. I clicked on on, and the cursor when to previous(GroupName ({Industrial_Step_Study_All.Name})) So I am assuming the problem lies within this area.

 

[synapsevampire]

Might be a first row problem, so try:

whileprintingrecords; // never hurts
if not(onfirstrecord) then
(if
GroupName ({Industrial_Step_Study_All.Name}) = previous(GroupName ({Industrial_Step_Study_All.Name}))
then {Industrial_Step_Study_All.BODCapacity}
else
Previous ({Industrial_Step_Study_All.BODCapacity}) + Previous({@BOD Adjusted Loadings (LBS per Day)})/18
)
else
0

-k

 

[Asha2004]

I am still getting the message that I had earlier when I tried this next post.

 

[lbass]

Try (Howard's formula with a "<>" instead of an "="):

if {Industrial_Step_Study_All.Name} <> previous({Industrial_Step_Study_All.Name}) then {Industrial_Step_Study_All.BODCapacity} //is this the initial
//value you want?
else
Previous ({Industrial_Step_Study_All.BODCapacity}) +
(Previous({@BOD Adjusted Loadings (LBS per Day)})/18)

You still have not said what the value for purchase capacity should be for the first record within each group. Does it make sense to have it equal the base capacity? Or to be the base plus adjusted loadings/18 for that record (not the previous one just for the first record in the group)? Or does it make sense for it to be 0? Maybe you could show in your example what you expect the value to be.

-LB

 

[tektipdjango]

1 - previous function have problems on the first record as there is no previous record so you have to check with onFirstRecord

2 - You can't use previous(groupName()) function, even with the check not onFistRecord as on the second record of the first group you have not previous group as you are still in the first group, so the check must be on the value
previous(name) and not on previous(GroupName(name))

3 - You say :
The problem with this is first of all, I need it to reset for each group to get the basecapacity

I assume you mean the basecapacity is a value that is specific for one name.
So the formula previous(BaseCapacity) will not work for the first record of each group as the value will not be previous(BaseCapacity) but the BaseCapacity for this record itself or even 0?
For this first record within each group you have the same problem with AdjustedLoadings. Is it previuos(AdjustedLoadings) or a new AdjustedLoadings (0 or AdjustedLoadings for the first record?)

Try something like (pseudo code) :

[COLOR=red]this is pseudo code[/color]
whileprintingrecords;   // never hurts
if not(onfirstrecord) then   
(if 
Name = previous(Name)
then
Previous (BaseCapacity}) + (Previous(AdjustedLoadings)/18
else
(previous(BaseCapacity) or BaseCapacity or 0 or ?) + (previous(AdjustedLoadings) or AdjustedLoadings or 0 or ?)/18 [COLOR=red] //Here you have to put the value you want for the first record of each group)[/color]
)
else
(BaseCapacity or 0 or ?) + (AdjustedLoadings or 0 or ?)/18
[COLOR=red]// this last line is for the first record of the file, you have to choose the value you want[/color]

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[Stoffel24]

Hi Code fixesers,

I am trying to get the first and last record in a group.
E.g Group Records
X 4300
X 100
X 3000

My answer should be 4300 after processing per group.

Please Help.

 

[Asha2004]

Here is the formula from the purchased capacity formula. This is not Psuedo. Now this works great except it is not keeping the new purchased capacity when it goes to the next record. I have created a mock report to show you what is actually happening.

Group 1: Name:Angelica

Details:
Date BaseCapacity adjustedloadings Purchase capac.
2/15/2003 311 95 311
4/15/2003 311 0 316 --This is correct
6/15/2003 311 0 311 --This is not correct it should actually be 316
8/15/2003 311 0 311 --This is not correct it should actually be 316
10/15/2003 311 0 311 --This is not correct it should actually be 316
12/15/2003 311 0 311 --This is not correct it should actually be 316

Formula: Purchased Capacity

whileprintingrecords;
if not(onfirstrecord) then
(if
{Industrial_Step_Study_All.Name} = previous({Industrial_Step_Study_All.Name})
then
Previous ({Industrial_Step_Study_All.BODCapacity}) + (Previous({@BOD Adjusted Loadings (LBS per Day)}))/18
else
{Industrial_Step_Study_All.BODCapacity}
)
else
{Industrial_Step_Study_All.BODCapacity}

 

[tektipdjango]

the problem is that you want the calculation not on the previous(BODCapacity), but on the previous(purchasedCapacity)

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[Asha2004]

Thanks alot tektipdjango . You are really helping me, but I get an error message when I changed the formula to what I think you are saying.

Here is the error message:
A formula cannot refer to itself either directly or indirectly

Formula

Whileprintingrecords;
if not(onfirstrecord) then
(if
{Industrial_Step_Study_All.Name} = previous({Industrial_Step_Study_All.Name})
then
Previous ({@Purchased Capacity}) + (Previous({@BOD Adjusted Loadings (LBS per Day)}))/18
else
{Industrial_Step_Study_All.BODCapacity}
)
else
{Industrial_Step_Study_All.BODCapacity}

 

[lbass]

Please provide another mock report so we can see what happens if there are several different values in the adjusted loadings column. It looks right now like you want the last value to be carried forward, but what happens if there is a new value in this column? Please show an example where we can see what you mean.

-LB

 

[Asha2004]

It is the same as the post before the last post I did. I can't get past the formula because of the error it is giving me.

 

[lbass]

I was suggesting that you make up an example. What would the purchase capacity column look like in the following example:

Date BaseCapacity adjloadings Purchcapac.
2/15/2003 311 90
4/15/2003 311 0
6/15/2003 311 90
8/15/2003 311 72
10/15/2003 311 0
12/15/2003 311 46

-LB

 

[tektipdjango]

LBass is right, we have not enough information to solve rapidly the problem.

I'll try anyway but I've not CR on my computer at the moment, so maybe I'll do somme errors...

What I understand is that in a group, the capacity is always constant (eg 311) and that the purchasedcapacity will increase with the sum on the adjustedLoadings

New Formula :
@sumAdjustedLoadings

Whileprintingrecords;
numbervar sumadjustedLoadings;

if not(onfirstrecord) then
(if
{Industrial_Step_Study_All.Name} = previous({Industrial_Step_Study_All.Name})
then
sumadjustedLoadings:=sumadjustedLoadings+Previous({@BOD Adjusted Loadings (LBS per Day)}

else

sumadjustedLoadings:=0

)
else

sumadjustedLoadings:=0


Then in the Formula: Purchased Capacity

Whileprintingrecords;
evaluateAfter ({@sumadjustedLoadings});
numbervar sumadjustedLoadings;
if not(onfirstrecord) then
(if
{Industrial_Step_Study_All.Name} = previous({Industrial_Step_Study_All.Name})
then
Previous ({Industrial_Step_Study_All.BODCapacity}) + sumadjustedLoadings/18)
else
{Industrial_Step_Study_All.BODCapacity}
)
else
{Industrial_Step_Study_All.BODCapacity}

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[Asha2004]

I am sorry. I posted this chart with my problems I was having in about the 5th or 6th post above this one. I have reposted it with what the purchased capacity should be. Also, with what it looks like now, and with the formula. Please let me know if you need anything besides this.

Thanks for all of your help. I am really happy that you are helping me, because I am on a deadline, and this is my last issue to deal with.



This is what the report should look like:

Group 1: Name:Angelica

Details:
Date BaseCapacity adjustedloadings Purchase capac.
2/15/2003 311 95 311
4/15/2003 311 0 316
6/15/2003 311 0 316
8/15/2003 311 0 316
10/15/2003 311 95 316
12/15/2003 311 0 321

This is what it does look like now:

Details:
Date BaseCapacity adjustedloadings Purchase capac.
2/15/2003 311 95 311
4/15/2003 311 0 316
6/15/2003 311 0 311
8/15/2003 311 0 311
10/15/2003 311 95 311
12/15/2003 311 0 316

Here is the formula for purchased capacity:

Whileprintingrecords;
if not(onfirstrecord) then
(if
{Industrial_Step_Study_All.Name} = previous({Industrial_Step_Study_All.Name})
then
Previous ({@Purchased Capacity}) + (Previous({@BOD Adjusted Loadings (LBS per Day)}))/18
else
{Industrial_Step_Study_All.BODCapacity}
)
else
{Industrial_Step_Study_All.BODCapacity}

 

[Asha2004]

Here is the formula for adjusted loadings:

If {Industrial_Step_Study_All.BODLBSperDay} > {Industrial_Step_Study_All.BODCapacity} THEN
{Industrial_Step_Study_All.BODLBSperDay} - {Industrial_Step_Study_All.BODCapacity} ELSE
0

 

[tektipdjango]

try my last method, I think you'll gat what you want

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​

 

[Asha2004]

I wanted to respond to the below because it is a little off, and the little off is where I think I am having my problem

POst from:

What I understand is that in a group, the capacity is always constant (eg 311) and that the purchasedcapacity will increase with the sum on the adjustedLoadings

Here is what I am trying to say... Hope this helps....

The capacity is a constant. the purchased capacity is the sum of the previous purchased capacity + (previous adjusted loadings/18).

You solved one issue which was dealing with the first record, and the first record in each group, now the second problem is dealing with the accumlated formula above. Now, I made a change upon your suggestion to get previous purchased capacity, (which I thought sounded like it would fix the problem, but when I did that I got an error message that says I can not refer to the same formula directly or indirectly.) That is where we are right now.

Once again, I am so sorry if I sound frustrated. I am a little because of crystal, not because of you guys. I REALLY apprecaite the help. I have been trying to work out this issue for two days.








New Formula :
@sumAdjustedLoadings

Whileprintingrecords;
numbervar sumadjustedLoadings;

if not(onfirstrecord) then
(if
{Industrial_Step_Study_All.Name} = previous({Industrial_Step_Study_All.Name})
then
sumadjustedLoadings:=sumadjustedLoadings+Previous({@BOD Adjusted Loadings (LBS per Day)}

else

sumadjustedLoadings:=0

)
else

sumadjustedLoadings:=0


Then in the Formula: Purchased Capacity

Whileprintingrecords;
evaluateAfter ({@sumadjustedLoadings});
numbervar sumadjustedLoadings;
if not(onfirstrecord) then
(if
{Industrial_Step_Study_All.Name} = previous({Industrial_Step_Study_All.Name})
then
Previous ({Industrial_Step_Study_All.BODCapacity}) + sumadjustedLoadings/18)
else
{Industrial_Step_Study_All.BODCapacity}
)
else
{Industrial_Step_Study_All.BODCapacity}

 

[tektipdjango]

your error is referring to my first post but a few minutes ago i sent you the post with the two formulas

this is this one you have to try now!

--------------------------------------------------
[highlight]Django[/highlight]
bug exterminator
tips'n tricks addict​