cancel
Showing results for 
Search instead for 
Did you mean: 

Macro function STDEV()

Former Member
0 Kudos

Hello Gurus,

I am trying to calculate the standard deviation of a all the values in a KF using the macro function STDEV(), and I am not getting the right results, when I check with the same caculation done on the values of the same KF in excel, I get a different value. I am using lets say 3 years of values in monthly buckets, should I be using a area of 36 values, or simply the row in the argument of the macro. ie: Result (Cell or Row)= STDEV(Row) or Result (Cell or Row)=STDEV(AREA (column1 to column 36). Using Row is not working so far and using area works , but gives a different result compared to excel. Also strangely the results are differnt when I write it to a real KF versus an auxiliary KF. Does anyone know how to make this work?

Thanks,

Rupa

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Hi Rupa,

I have always found that the statistical functions in the macro work bench are quite a black box. If you give 10 values and ask for standard deviation using the macro function STDEV(), then the system just doesn't do it like excel. What excel does is straight forward calculation. But in the macro function, I guess it takes some 3 or 4 values at a time and then calculate standard deviation and then take that along with other values to generate another standard deviation and so on. We tried this in a project and since the results were not reliable, we abandoned it. We haven't tied with auxilary keyfigures....but because of the logic it's using, I think it is bound to go wrong with auxilary keyfigures as those values are not really stored in database.

If any body understands the way these Statistical functions work, please update us.

Former Member
0 Kudos

Thanks Visu for sharing your views, Its even more wierd that the simplest calculation like MEAN is showing different results when compared to the one calculated using Excel starting the 3rd period onwards.

Mean calculated using the macro function MEAN(AREA) is matches only for first 2 periods of that obtained using Excel using AVERAGE(cells). Is there any alternate way to calculate the Standard deviation in DP accurately? Since this is something very basic for a stat forecasting software.

Thanks

Rupa

Former Member
0 Kudos

Hi Rupa,

The concept of standard deviation in probability theory is of 2 things,

1. For complete population

2. Sample population

The calculation of SD varies based on which type of data set you use in calculation. If you have complete set of data then the denominator of data will not have n-1, rather it is taken as n alone. If you use sample of data (ie., unbiased as they call in probability), the n-1 is considered. If you were using STDEV of MS Excel, then it uses n-1 considering that the data which is input is a sample. However in SAP its different if you use STDEV() in APO it considers whole population. Thats the reason you get different results in Excel and SAP

So if you want to calculate SD for sample, then use E_STDEV() {called as estimated SD) in APO which corresponds to STDEV in MS Excel

If you need whole population then, use STDEV() function in APO which corresponds to STDEVP in MS Excel.

Thanks,

Rajesh Manoharan

Edited by: RAJESH MANOHARAN on Dec 23, 2009 10:10 AM

Former Member
0 Kudos

Thanks for the brilliant clarification, Now atleast the first bucket matches with the results of that from Excel 's STDEVP, but for the remaining periods the values are still different when we caluclate the MEAN or the STDEV and compare it with Excel.

One another quetion to this end:

We have the formula of TOLERANCE LANE = EXPOST FCST +/- SIGMA * MAD, my question is , the SIGMA used here is just a factor that we can change anytime(the default being 1.25 as SAP says), or is this the Standard Deviation of the history values, so that the formula then becomes TOLERANCE LANE = EXPOST FCST +/- SIGMA factor(1.25)STANDARD DEVIATION MAD. I guess the former is true. But I need to make sure that the SIGMA is just a factor and not the Standard Deviation(STDEV) itself.

Also is there anyway, we can use the Standard Deviation in place of the MAD in the calculation of the Outliers? in which case the the formular becomes TOLERANCE LANE = EXPOST FCST +/- SIGMA factor (1.25 or any value that is maintained) * STANDARD DEVIATION

Thanks again,

Rupa

Former Member
0 Kudos

Hi Rupa,

I don't understand the meaning of first bucket and remaining periods. Lets say you have a data series of 10,15,20,15,10. Now when I calculate MEAN its for all these 5 data. You should get MEAN common for all the 5 buckets. Same holds good for SD as well. So whether you try to get it for a cell or a row the value will be same. Hope I understood your question properly.

For the second question, YES Sigma is a factor and NOT standard deviation. I'm not sure whether any BADI is available to tweak the forumla. But you can always do with a complex macro and outlier corrections involving custom KFs. Ofcourse it will be manual and can be automated with macro events if required.

Thanks,

Rajesh Manoharan

Answers (0)