Average and Average ifs excel function in tm1

[Analytics123]

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,

[lotsaram]

so what's the question?

[Analytics123]

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.

[tomok]

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.

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]

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 .