Hi ,
I am planning to build a credit scoring model in tm1 based on last 24 month sales of customer . Ideally this should have been done in excel after exporting the data in tm1.But however i wanted to try if the calculations are able to performed in tm1 .
Calculation 1 :
I will have 24 months of sales and I have to compute the average sale of this month.It should not include sales with zero values .
say if only 22 months has sales = 22 months sales number /22
I will need the formula to achieve this calculated measure average .so the rule has to do this .
Calculation 2:
Use similar to excel's AverageIFs calculation
while doing average check if each month sales is less than 3 times the 24 month average value and greater than zero.
Averageifs(24 month sales,24 month sales, <3*Average& >sales>0)
This calculation is to remove the outliers.
Thanks,
Average and Average ifs excel function in tm1
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
-
- MVP
- Posts: 3653
- Joined: Fri Mar 13, 2009 11:14 am
- OLAP Product: TableManager1
- Version: PA 2.0.x
- Excel Version: Office 365
- Location: Switzerland
Re: Average and Average ifs excel function in tm1
so what's the question?
Please place all requests for help in a public thread. I will not answer PMs requesting assistance.
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Average and Average ifs excel function in tm1
I just want to know if there is an equivalent formula in tm1 to write the rule for the below calculation 1 and calculation 2 .
If so can you please let me know how to write that calculation.
If so can you please let me know how to write that calculation.
-
- MVP
- Posts: 2832
- Joined: Tue Feb 16, 2010 2:39 pm
- OLAP Product: TM1, Palo
- Version: Beginning of time thru 10.2
- Excel Version: 2003-2007-2010-2013
- Location: Atlanta, GA
- Contact:
Re: Average and Average ifs excel function in tm1
No, there isn't. Those formulas require any array of inputs and all existing TM1 formulas work on a single cell. TM1 has no knowledge of time-series, You have to create that all yourself. What I mean is that you have to break down what the AVERAGE formula does in Excel into it's components and then re-create those components in TM1. From there you can re-construct in TM1 what the AVERAGE formula does. This is true of most of the financial and statistical functions in Excel that you would like to mimic in TM1. Good luck.Analytics123 wrote: ↑Fri Sep 29, 2017 3:43 pm I just want to know if there is an equivalent formula in tm1 to write the rule for the below calculation 1 and calculation 2.
-
- Posts: 128
- Joined: Tue May 23, 2017 12:45 pm
- OLAP Product: Tm1
- Version: 9.5
- Excel Version: 2010
Re: Average and Average ifs excel function in tm1
okay ima trying to replicate average if not equal to zero in tm1 rule .
May be i wil do it for 2 months first .
I dont think so its straightForward .It will have multiple ifs for 2 months only .
May be i wil do it for 2 months first .
I dont think so its straightForward .It will have multiple ifs for 2 months only .