Variable for WACD

[GeorgeOrwell1984]

I'm trying to write a weighted average cost of debt report.

This is very easy to calculate in excel using:

sumproduct(a1:a15,b1:b15)/c15 or as per example below

I have the Values and multiplying factor coming in on the details lines and have used a simple formula (which I will call Weighted value)to multiply one by the other also on the Details line.

The details line is Grouped by Country

I have used a Whileprintingrecords 3 step variable to provide me with a Total in the Country Footer

What I want to do is get a grand total of this value in the Report footer.

This is proving very difficult. I am able to get a total in the RF but the total seems to double up the last details record in each group and gives me a false total.

I have tried to use next or last record to identify the last details record in each Country Group so that I can deduct this value from the Grand Total calculation but am unable to do this because of the data execution order.

e.g

Spain
Value Rate Weighted
30,000.00 2.5 75,000.00
12,000.00 1.5 18,000.00
150,000.00 1 150,000.00
95,000.00 3 285,000.00
Sum 287,000.00 8 528,000.00
WACD 1.8397
Mean Avg 2.0000
UK
Value Rate Weighted
100,000.00 2.5 250,000.00
50,000.00 1.5 75,000.00
100,000.00 1 100,000.00
75,000.00 3 225,000.00
Sum 325,000.00 8.00 650,000.00
WACD 2.0000
Mean Avg 2.0000

Grand Total 612,000.00
WACD 1.9248


Formulá's

Spain
Value Rate Weighted
30000 2.5 =C9*D9
12000 1.5 =C10*D10
150000 1 =C11*D11
95000 3 =C12*D12
Sum =SUM(C9:C12) =SUM(D912) =SUM(E9:E12)
WACD =SUMPRODUCT(C9:C12,D912)/C13
Mean Avg =D13/4
UK
Value Rate Weighted
100000 2.5 =C18*D18
50000 1.5 =C19*D19
100000 1 =C20*D20
75000 3 =C21*D21
Sum =SUM(C18:C21) =SUM(D1821) =SUM(E18:E21)
WACD =SUMPRODUCT(C18:C21,D1821)/C22
Mean Avg =D22/4

Grand Total =C22+C13
WACD =(C13*E14+C22*E23)/C26

Can anyone help?

Is there an actual operator or fucntion within Crystal for this...if so how does it work

Any help much appreciated

 

[lbass]

Right click on your formula/fields in the detail section and insert summaries (sums) on them at the group and the grand total level to get the sums.

WACD would be (at the group level):

sum({@weighted},{table.country})/sum({table.value},{table.country})

At the grand total level, it would be:

sum({@weighted})/sum({table.value})

Mean average would be (group):

sum({table.rate},{table.country})/count({table.rate},{table.country})

//{@grandtotalrate:
sum({table.rate})/count({table.rate})

-LB

 

[GeorgeOrwell1984]

Ibass

Thanks for the Response

I am not able to use the summary function because one of the primary parts is a variable from a sub report.

10,000.00 * 1.5 = 15,000.00

The 10k comes direct from a DB field...the 1.5 is a SharedNumbervar from a sub report hence I can't use the summary function (if only I could)

So because of this i have used a normal formula at the details level to get me the multiplied figure of 15K and I have then used a whilePR Var to add up all the mulipliers on each detail line and give me the country total multiplier at the Group level.

I then Divide this by the original value group total which is where I have used the insert summary function to give me a toal at the Group level which gives me the WACD at Group.

I did try using the Weighted average function but again this does not seem to work because one part of the array used is a shared var from the sub report.

Best Rgds
George

 

[lbass]

That's a very critical piece of information to have omitted. Please show the content of the variable formulas you are using. It sounds to me like you added the formula you used in the detail section to the Report Footer, causing it to execute again. For the results formula, you should create a new formula that merely references the variable.

-LB

 

[GeorgeOrwell1984]

LB

Thanks for your help.

I kinda understand what you mean in your last statement.

I have used Crystal for years but have never had any formal training in it....so I'm a bit more trial an error than logical progression in the order of the report build up.

Whilst I have used Crystal for some time and understand basic variables I am by no means a variable master!

Var on subreport is:

Shared NumberVar CurRate;
CurRate:={EVENTS.RATE}
which pulls in multiplication factor to details line
-----------------------------
The variable that references it is:

Shared NumberVar RowsPerCountry;
RowsPerCountry := RowsPerCountry + 1;

Shared NumberVar SumWeightedRate;
Shared NumberVar SumRate;
Shared NumberVar CurRate;
SumWeightedRate := SumWeightedRate + ({MMDEALS.REMAIN_FV}*CurRate);
SumRate := SumRate + CurRate;
CurRate

In the Group Header we have

Shared NumberVar RowsPerCountry;
RowsPerCountry:=0;

shared numbervar SumRate;
Shared NumberVar SumWeightedRate;
SumRate:=0;
SumWeightedRate:=0;

shared numbervar SumMargin;
Shared NumberVar SumWeightedMargin;
SumMargin:=0;
SumWeightedMargin:=0;

To recap all i'm after is the total of the sum weighetd rate in the RF.

I think all of the building block are there just can''t seem to get the figure I wnat on teh Rpt footer.

Thaansk once again for any help you can give

 

[GeorgeOrwell1984]

LB

Further info

-£59,400,000.0000
-£2,000,000.0000
-£14,000,000.0000
-£12,000,000.0000
-£8,000,000.0000 last detail line of group
-£95,400,000.0000 total Group

15-04-2017


£0.0000
-£14,482,801.3463
-£32,104,800.0000
-£600,000.0000
-£22,848,961.0380
£0.0000
-£134,700,000.0000
-£106,600,000.0000
£0.0000
-£39,784,706.7600
-£4,000,000.0000
-£2,400,000.0000 Last detail ine of group
-£357,521,269.1443 Total Group

01-10-2020

-£33,497,250.0000
-£20,624,000.0000
-£10,725,000.0000
-£12,825,000.0000
-£20,194,006.7100
-£17,850,059.4960
-£27,000,000.0000
-£26,610,227.9040
-£80,000,000.0000
-£28,075,355.6200
-£5,740,894.3800
-£21,433,750.0000
-£73,440,000.0000
-£47,500,000.0000
-£1,379,216.8200
-£8,891,128.7700
£0.0000
-£20,854,992.0500
-£70,878,820.1700
-£24,899,992.6800 Last detail line of Group
-£552,419,694.6000 Total Group

15-11-2001


-£1,005,340,963.7443 Total I want!!!!!!!
£35,299,992.6757 Sum of last record in each details line
-1,040,640,956.42 Total I get returned in crystal rpt footer

The variable it seems is double counting the last details line of each Group.

 

[GeorgeOrwell1984]

LB

Solved it I believe.

Your comments helped me to resolve in a new set of eyes kind of way.

Going to tidy it up and then will post solution in the spirit of the forum

Rgds
George84

 

[GeorgeOrwell1984]

LB and All

WACD Solution

I'm sure LB can tidy this up to make more sense and I think I have over done or doubled up on some of the variables.

BUT it now works!

If your used to using Cyrstal and fiddle around a bit I'm sure you'll get it working the same as I have using some or all of below.

Thanks to LB for steering me in correct direction.



Sub Report...That is if you need one

Shared NumberVar CurRate;
CurRate:={xMultiplication Factor}

Main Report

GHeader1a

Shared NumberVar RowsPerCountry;
RowsPerCountry:=0;

shared numbervar SumRate;
Shared NumberVar SumWeightedRate;
SumRate:=0;
SumWeightedRate:=0;

GHeader1b

WhilePrintingRecords;
shared numbervar GTResetBase := 0;

Details A

Shared NumberVar CurRate;
CurRate:=0;

Details Main

{Value you want to weight}
{x Multiplication Factor}

sum({Value you want to weight}x Multiplication Factor} )

GFooter1A
WhilePrintingRecords;
NumberVar GTResetBase;
GTResetBase:=GTResetBase + Sum ({Value you want to weight}, {Group level})*{@a_WacdDivisionBase}

N.B @a_WacdDivisionBase =
If{@a_WacdBaseSum}= 0 then 0
else
{@a_WacdBaseSum}/sum({Value you want to weight}x Multiplication Factor} )

GHFooter1B
If{@a_WacdBaseSum}= 0 then 0
else
{@a_WacdBaseSum}/Sum ({Value you want to weight}, {Group level})

NB. {@a_WacdBaseSum} =
WhilePrintingRecords;
Shared NumberVar BaseReset;

RFooter
WhilePrintingRecords;
NumberVar GTResetBase;

RFooter
{@A.GTWeightBase_RF}/Sum ({Value you want to weight})

 

[lbass]

I think you have made it more complicated than necessary, as I can't quite follow what you are doing, since you are referencing formulas that you have not named for us. I'm confused by your need for GTResetBase. If you are trying to sum something at the grand total level, you would not use a reset. You ordinarily only reset something at the level you want a result, so for example, if you wanted a group total, you would use a reset in the group header, an accumulation formula in the details section, and display the results in the group footer, like this:

//{@reset-GH}:
whileprintingrecords;
numbervar x := 0;

//{@accum-details}:
whileprintingrecords;
numbervar x := x + {table.amt};

//{@display-GF):
whileprintingrecords;
numbervar x;

-LB