Weighted average/reverse running sum 1

[emailswe]

Hi,

I would like to get a weighted average field on a set of data in crystal v. 11.
Mathematically one may either have a reversed running sum field or a matrix (sum product in excel syntax). Do any one have any ideas how to proceed, my forehead are about to burst after banging the wall...

Data looks like
Period CF marg CF*marg Rev w_marg rev CF w marg
2009 03 10 000 0,13 1300 9000 40 000 0,225
2009 04 10 000 0,17 1700 7700 30 000 0,257
2009 05 10 000 0,20 2000 6000 20 000 0,300
2009 06 10 000 0,40 4000 4000 10 000 0,400

The last column is Rev w_marg / Rev CF. The purpose is to evaluate how fast, interms of months, the margin on new sales impact the average margin on the sales.

Plz help me

 

[IanWaterman]

Need to know what you want data to look like and the basis of your calculation.

Ian

 

[emailswe]

Sorry Ian for not beeing "crystal-clear"

the column I miss in my report is the last one, how I get there is of les importance. The columns that I am able to generat as of now is teh first 4, Period to Cash flow* margin.

An other piece of information that I forgot is that the data presented is in group footer since I have more than one cash flow each month.

swemail

 

[IanWaterman]

If none of the fields in the Grp Footer are summaries you can just use a formula as the Grp Footer will contain the last record.

@W marg
{Rev w_marg}/ {Rev CF}

Is that what you want?

Ian

 

[emailswe]

That is the ultimate goal, however I do not have the reversed summmaries in the group footer. That's the $1m question I have tried to solve.

To my knowledge that would be quite difficult since qrystal is "printing" the reports detail level by detail level. But I guess that there is a twist to get the report to sumarize backwards.

swemail

 

[IanWaterman]

As I asked in my first post, from your set of details how would you calculate the RevCF.

Please show some sample data and what you want to see

For example expand this dataset

2009 03 10 000 0,13 1300 9000 40 000 0,225

Showing details and how you would like to calculate RevCF.

Ian

 

[emailswe]

Than you Ian for support and I am sorry that I don't really make my self understood.

The fields
period CF Margin
2009 03 10 000 0,13

Are known (retrieved from database), the field CF*Margin is simple
//@W_Margin
{CF}*{Margin}

The issue is how to get the reversed summary of, firstly, the @W_Margin} and, secondly, the CF column. In the first example the reversed summary for CF would be:
Period CF Revesed summary CF
2009 03 10 000 40 000
2009 04 10 000 30 000
2009 05 10 000 20 000
2009 06 10 000 10 000

For the first row I would like to have the total summary of CF, i.e. 40 000, the second the summary of all CF's BUT the first, i.e. 30 000 etc... thet is the what I do not get/know crystal to do.

swemail

 

[IanWaterman]

Not sure why you are doing this but you can achive this by grouping data by year. sort data by period ascending.

If you do not have year use formula

@year
left(period, 4)

Use a variable to store Revesed Summary

In year header
@init
whileprintingrecords;

global numbervar RevCF;
global numbervar periodcount:=0;

RevCF:= sum(CF, year)

In detail
@eval

whileprintingrecords;

global numbervar RevCF;
global numbervar periodcount;

If periodcount <> 0 then
RevCF:= RevCF - CF;

periodcount:= periodcount+1

You can hide this formula by suppressing in format field

In

Detail add new formula

@W marg
whileprintingrecords;

global numbervar RevCF;

{Rev w_marg}/ RevCF;

Ian

 

[emailswe]

Thank you!
i will give u a * fort that!