how to use average function ?

[mirmidone]

I want to use Average funct. for only values > 0.
How to write the expression in Average funct ?
Or how to average only values > 0 ?
For ex. I Have following values: 30 - 20 - 0 - 15 - 0
in normal average I have (30+20+0+15+0) / 5. this is not correct !!
I want this average: (30+20+15) / 3
Many thanks for resp..
Enio Donci from Italy

[Alex K.]

Hello,

Please try to use the following expression:

Code: Select all

{AvgIf(DataSourceName.ColumnName,DataSourceName.ColumnName != 0)}

Thank you.

[Designer]

Hi Aleksey,
I am looking to get Average for 3 Financial statement years, my formula is not working, can you please help me to identify issue.

{AvgIf(Optimist.Legal_Entity.Model.Current_Yr.Commercial_and_Industrial___EBIT___Value,
Optimist.Legal_Entity.Model.Prior_Yr.Commercial_and_Industrial___EBIT___Value,
Optimist.Legal_Entity.Model.Prior_Prior_Yr.Commercial_and_Industrial___EBIT___Value !=0)/1000}

Thanks
Pat

[HighAley]

Hello.

The Avg is an aggregate function. It's impossible to use it this way.
Please, try to use simple arithmetic operators.
Could you specify how this function should work?

Thank you.

[Designer]

Hi
I want to calculate Average of 3 values, these values are from 3 financial year statements in most cases but if the user has choose only 2 years it should average of two years.
I am building interactive report, we have few scenarios; e.g. May not have all 3 years data point, 0 value or negative values and in any case I don’t want to get calculation or division error.
Thanks

[Designer]

Hi,
I have used this formula
{Totals.Avg(Optimist.Legal_Entity.Model.Historical_Current_Yr.Commercial_and_Industrial___EBIT___Value,
Optimist.Legal_Entity.Model.Historical_Prior_Yr.Commercial_and_Industrial___EBIT___Value,
Optimist.Legal_Entity.Model.Historical_Prior_Prior_Yr.Commercial_and_Industrial___EBIT___Value)}
getting error
"There are compilation errors in the Report Template. The tope 1 errors of 1 are : --- Text 182: No overload for method 'Avg' takes 4 arguments"

Can you please help?

Thanks

Pat

[HighAley]

Hello.

Please, use next expression:

Code: Select all

(value1+value2+value3)/3

You could add additional check if you don't need to calculate null values.

Thank you.