Page 1 of 1
Standard Deviation in TM1
Posted: Wed Feb 04, 2009 4:35 pm
by BigDSter
Hi all,
Just a quick question. Am currently building a safety stock calculator that was previously in Excel but am replicating in TM1 to make data collection and entry a lot easier.
I can do most of the stuff, just coming a bit of a cropper with one calc.
I need to work out the standard deviation of the forecast error, over a period of last 52 weeks. Simple enough I thought, but there doesn't appear to be a stdev function in TM1 rules, and not quite sure how to use the MDX one. I'd quite like to keep it all in TM1 so would prefer not to wimp out to excel, but the alternative at the minute looks like calculating stdev from first principles, which seems a tad messy.
Hope someone can help, forgive me if I've missed something obvious.
Ta
Re: Standard Deviation in TM1
Posted: Wed Feb 04, 2009 4:55 pm
by John Hobson
Hi David
I did this to calculate store grades based on a bell shaped curve in TM1
In the "Autocluster"cube containing the space by store I had
Code: Select all
['Count'] = N: 1;
['Square Space'] = N:['Space'] * ['Space'];
and in another higher level cube where I did the calc I had
Code: Select all
['Std Dev Space'] =
SQRT(
(DB('AutoCluster',!Plan Version', !Prod_D, !Time Season, 'All Base Stores', 'Square Space')
*DB('AutoCluster', !Plan Version', !Prod_D, !Time Season, 'All Base Stores', 'Count'))
-(DB('AutoCluster', !Plan Version', !Prod_D, !Time Season, 'All Base Stores', 'Space')
*DB('AutoCluster', !Plan Version', !Prod_D, !Time Season, 'All Base Stores', 'Space'))
)
\ DB('AutoCluster', !Plan Version, !Prod_D, !Time Season, 'All Base Stores', 'Count');
I'm no statistician but it seemed to work
Re: Standard Deviation in TM1
Posted: Wed Feb 04, 2009 4:59 pm
by BigDSter
Ta for that John,
bit late in the day to figure it out, but I'll have a crack at it tomorrow when I'm more awake.
Thanks
Re: Standard Deviation in TM1
Posted: Wed Feb 04, 2009 5:11 pm
by ScottW
Standard Deviation and other basic statistical functions is something that TM1 doesn't do well. Calculating averages and counts are OK but StDev is a real chore. It's doable if you do the algebra from 1st principles a la Mr Hobson but this will be a high overhead rule to calculate. IMO if the dataset is big then easier and better performance to pipe out into an SQL database, do the calculation there and pipe it back in.
I'm not sure that TM1's implementation of MDX would handle standard deviation, if you get it to work please let me know!
TM1 does handle MAPE very well which is the generally accepted best practice measure for demand planning forecast accuracy. Are you taking the next step with deriving theoretical safety stock via Orlicky & Plossl's method from the mean absolute error? ... shouldn't your MRP/ERP system be able to spit this out for you? (assuming the demand plan is loaded in?) It is a straightforward calculation in Excel, the problem in TM1 is really just the stDev as you have already figured!
I'm not sure that that helps ...
Re: Standard Deviation in TM1
Posted: Thu Feb 16, 2012 4:48 pm
by gavod
Hi,
Was the STDEV issue addressed in newer releases? Is shows up on the supported functions in the IBM documentation for 9.5.1.
If so , what is the synax?
Thanks.
Re: Standard Deviation in TM1
Posted: Thu Feb 16, 2012 6:20 pm
by David Usherwood
It's supported as an MDX function. Since there's no sensible way I've found to do anything with MDX except to drive dynamic subsets, the expression 'chocolate teapot' comes to mind. If the community have worked out how to bypass this I'd be delighted.
Re: Standard Deviation in TM1
Posted: Fri Feb 17, 2012 3:34 am
by Andy Key
Obviously it would be preferable if TM1 would do it for you, but Standard Deviations are actually quite easy to code from first principles. I don't have a copy of the system that I used it in with me, but I'll dig it out over the weekend.
Re: Standard Deviation in TM1
Posted: Fri Feb 17, 2012 6:28 am
by ravi
Hi
There are clients planning to have IBM SPSS as -IBM Cognos TM1 can be extended with IBM SPSS Modeler to provide extended statistical modeling.Please provide your comments.
Regards,
Ravi
Re: Standard Deviation in TM1
Posted: Fri Feb 17, 2012 7:50 am
by David Usherwood
@Andy - indeed, that's what I generally do. (I've recently had to built MMULT in TM1).
@ravi - what have you been told about SPSS/TM1 integration? I don't think there is any at present.
Re: Standard Deviation in TM1
Posted: Mon Feb 20, 2012 8:56 am
by Andy Key
@David - Never doubted it for a minute!
My method basically boils down to the same as John's. I actually wanted to do a Population SD rather than the Sample SD calculation shown above, but I had to go with the Sample SD as I had to tie it back to the client's Excel spreadsheet.
Yay Excel.