Percent Profit in CR XI 1

[Deb100]

Dear All

I need your help.

My source is an Excel spreadsheet and am using Crystal Reports XI.

My report is grouped as follows:

Group 1: client name
Group 2: stock

I have set up a formula field to sum the Net+/-Charges for each group of Stock for each client (@SubTotal Net +/- Charges), which is placed in Group Footer 2.

Example:

Bloggs, Fred
Date Stock Net+/-Charges
10/09/06 JPMorgan 10,000
11/09/06 JPMorgan -12,000
2,000

Now, below @SubTotal Net +/- Charges (currently 2,000) I need to show the percentage profit.

I have tried this formula:

if {@SubTotal Net +/- Charges} = 0 then 0 else
{table.Net +/- Chges} % {@SubTotal Net +/- Charges}

but it seems to be showing the percentage of all the JPMorgan stocks, and not just for the client and stock.

Any help would be appreciated.

Regards

Deb100

 

[lbass]

You neglected to show your formula which should look like:

//{@SubTotal Net +/- Charges}:
sum({table.Net+/-Chges},{table.stock})

-LB

 

[synapsevampire]

Percentage of what, the total for the client?

I'll eliminate your formula because you didn't share what's in it and use the usual means, try:

if sum({table.charges},{table.stock}) = 0 then
0
else
sum({table.charges},{table.stock}) % sum({table.charges},{table.client})

If this isn't what you want, state the percentage of what.

-k

 

[Deb100]

Hi, I have tried this formula :

if sum({table.Net +/- Chges},{table.Stock}) = 0 then
0
else
sum({table.Net +/- Chges},{table.Stock}) % sum({table.Net +/- Chges},{@Group Sort Client and Initials})

where '@Group Sort Client and Initials' is {table.Client}&", "&{table.Initials}

My Selection Formulas are:
Group Selection - maximum({table.Details},{table.Stock}) = "Sold"
Record Selection - {table.Details} in ["Sold", "Purchase"]

The stock-holding is also displayed. The Purchase-stock-holdings can be more than the Sold-stock-holdings, eg purchase 10,000 and sold 6,000 therefore 4,000 is still being held.

I need to show the percent profit for the 'Net +/- Charges' for the stock, for the client, also allowing for the fact that not all the stock-holding would have been sold.

I would be grateful for any help you can give me.

Regards

 

[lbass]

Please show some sample data and how you would expect the formula results to look. This is hard to follow just verbally.

-LB

 

[Deb100]

Hi, here is the additional info, as requested. Thanks in advance.

Example Data

DATE......CLIENT..INITIALS..DETAILS...STOCK................HOLDING..PRICE..NET +/- CHARGES
05/04/05..Smith...A............Purchase..ABC Investments...24200.......0.83....20315.48......
01/10/05..Smith...A............Sold.........ABC Investments..-24200......1.02...-24523.87......
10/02/05..Jones...B............Purchase..DEF plc................ 24800.......0.81....20314.46......
18/08/05..Jones...B............Sold.........DEF plc................-24800......0.94...-22930.12......

Example Result

Smith,A
DATE........STOCK...............HOLDING..PRICE..NET +/- CHARGES
05/04/05..ABC Investments...24200......0.83....20315.48......
01/10/05..ABC Investments..-24200......1.02...-24523.87......
.....................................................................4208.39......
........................................................................20.72%.....

Jones,B
10/02/05..DEF plc............... 24800......0.81... 20314.46......
18/08/05..DEF plc...............-24800......0.94...-22930.12......
.....................................................................2615.66......
........................................................................12.88%.......

 

[lbass]

Is the example data at the detail level or is Net+/-Charges a summary?

-LB

 

[Deb100]

Yes, the example data is at detail level. Net+/-Charges is NOT a summary.
Deb100

 

[lbass]

Your math seems to be a bit off for your second example, if what you are trying to do is show the sum of the two charges divided by the charge for the purchase. Try the following. First create a formula:

//{@purchase}:
if {table.details} = "Purchase" then {table.net+/-charges}

Then in the group footer for stock, place the following formulas:

//{@subtotal}:
sum({table.net+/-charges},{table.stock})

//{@percentprofit}:
if sum({@purchase},{table.stock}) <> 0 then
sum({table.net+/-charges},{table.stock}) % sum({@purchase},{table.stock})

-LB

 

[Deb100]

LB, I can't thank you enough - this has been driving me nuts!

It works perfectly now.

Thank you.

Deb100