How to get the slope -value of linear regression trendline 1

[Miklah]

Hi,

What I need is to get the slope of a linear regression trendline.
I know it can be shown on the chart, but I need it as a value that can be used in e.g. formulas outside the chart.

Is it possible with some command to get it?

If not, then is there some other solution to this other than calculate the slope myself (I already tried it but got problems with the "This field cannot be summarized" message).

I'm using Crystal Reports XI.

Thank you in advance for answers.

Best Regards,

Mikael

 

[lbass]

I think you would have to calculate this yourself. For help, you should supply the basics of the calculation and some sample data showing the expected result.

-LB

 

[Miklah]

Thanks for your answer!

So I have to do the calculations myself. What I want to do is to get the slope of the linear regression trendline so that I can calculate a forecast of future values. Eg. what is the traffic of some interface after 1 month or after 3 months.

In my reports I have the following grouping:
-Group1: Devices
-Group2: Interfaces
The things that I report are eg. traffic or alarm count over time (DateTime values) for an interface. So here are two different cases:

1) values straight from the database (e.g traffic for 1 day (not summarized, the database has values for on day)
2) summarized values; e.g. alarm count for 1 day

My problem is the second case AND reporting over time.

For using e.g. this formula (

http://en.wikipedia.org/wiki/Simple_linear_regression)

to calculate the slope of the linear regression trendline I need to have the DateTime values converted to numbers.

--> I created a formula field 'x_i':
DateDiff("d",minimum({timestamp}),{timestamp})
to get the day as a number starting from the first timestamp for every record. So now I have the values 0, 1, 2, 3, 4, 5, 6 etc.

But when I try to e.g. take an average of 'x_i' (to be used in calculation of the slope) I get the "This field cannot be summarized". This same problem occurs also in the case 2) mentioned above.


Here's a little example with sample values.

September 1st 10
September 2nd 25
September 3rd 30
September 4th 20
September 5th 40
September 6th 32

...the slope of the linear regression trendline for this example would be 4,14 and using that I could make a forecast in my report and get the following values:

September 7th ~41
September 8th ~45
September 9th ~49


How can this be solved? According to what I have read I think I have to use variables in some way, but how?

Best Regards,

Mikael

 

[dgillz]

How did you create your average? If it is a formula, please post it.

Software Sales, Training, Implementation and Support for Macola, eSynergy, and Crystal Reports

http://www.gainfocus.biz

"What version of URGENT!!! are you using?

 

[lbass]

A comment--when you use:

DateDiff("d",minimum({timestamp}),{timestamp})

..you are comparing the minimum for the entire report, not per some group. If you want it per the interface group, then you need to use:

DateDiff("d",minimum({timestamp},{table.interface}),{timestamp})

To get an average here, you will have to use a variable, with formulas like this:

//{@reset} to be placed in the interface group header:
whileprintingrecords;
numbervar amt;
numbervar daycnt;
if not inrepeatedgroupheader then(
amt := 0;
daycnt := 0
);

//{@accum} to be placed in the detail section:
whileprintingrecords;
numbervar amt := amt + {table.amt}; //whatever you are summarizing goes here.
numbervar daycnt := daycnt + 1;

//{@displave}:
whileprintingrecords;
numbervar amt;
numbervar daycnt;
shared numbervar ave;
ave := amt/daycnt;

I made the average variable shared, because I think you will need to calculate the averages in a subreport in the interface group header so that they are available for calculations at the detail level, which again will need to be done with variables, I think. Without using a subreport, the average is only available in the group footer.

-LB

 

[Miklah]

Thanks for your answer which was very helpful. Now I got it to work like I wanted.