# Calculating average, ignoring value 0

This site uses cookies. By continuing to browse this site, you are agreeing to our Cookie Policy.

• # Calculating average, ignoring value 0

After my former problem concerning the AVERAGE() function in rules should be fixed in the next release of Jedox 3.3 (see Calculate average - trouble with business rules) I have another question. Is there are possibility to ignore the value 0 when calculating the average of a couple of values?

AVERAGE(170, 190, 0) is 120 but I want it to be 180 because the last value should be ignored.

Maybe there is somebody outside who knows a solution for this problem?!
• do you try to average on a consolidated level ? meaning, for instance: Europe=average of all countries ?
laloune

Post hoc, non est propter hoc
• ### laloune wrote:

do you try to average on a consolidated level
What would be the average of a non-consolidated level?
I think the problem here is typical for BI. You have to be able to find out if a value is zero because that product hasn't been sold in the month in question or if the data for the month hasn't been loaded yet and is therefor not available. That makes a big difference when calculating averages or min/max.
Zero: avg(Q1) = (10 + 15 + 0) / 3
N/A: avg(Q1) = (10 + 15) / 2
Robert Tischler
Senior Consultant Vector SW DV GmbH
• As tish1 said the problem is that there is no difference in Jedox/Palo between the value 0 and for instance NULL as an indicator for a value that is not set yet. But even if I assume the 0 stands for NULL I'm searching for a mechnism to ignore these values like this is done in relational databases. In relational databases there is a difference:
- AVG(10, 20, NULL) = 15
- AVG(10, 20, 0) = 10

While writing the following lines I got a new idea: is is possible to determine the number of elements of a subset? If this ist possible you could create a subset that filters of elements > 0 and than you divide the sum of a dimension by the number of elements of the subset:
- AVERAGE_IGNORING_0 = SUM(dimension) / COUNT(subset)
• ### kolo wrote:

AVG(10, 20, NULL) = 15
I think, that a relational database would return NULL in that case.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• ### tish1 wrote:

What would be the average of a non-consolidated level?

### Source Code

1. ['average']=AVERAGE(['member1'],['member2'],['member3'])

or average the consolidated level (for instance, in Regions dimension from Demo, South = average of southern countries). And this rejoins the issue with 0 values

in the latter case, what I usually do it setting a rule on the base level to count whether the element has to be taken into account or not:
for instance: if Turnover, Average Turnover and Count are elements of dim Measures:

### Source Code

1. ['Count']=B:IF(['Turnover']==0,0,1)

then

### Source Code

1. ['Average Turnover']=C:['Average Turnover'] / ['Count']

While writing the following lines I got a new idea: is is possible to determine the number of elements of a subset? If this ist possible you could create a subset that filters of elements > 0 and than you divide the sum of a dimension by the number of elements of the subset:
- AVERAGE_IGNORING_0 = SUM(dimension) / COUNT(subset)
yes, PALO.SUBSETSIZE counts the number of elements returned by a subset. very practical to define print areas for instance
laloune

Post hoc, non est propter hoc
• Hi laloune,

can you say something regarding the performance of your IF-rule on base level in larger models? Especially the difference between "with" or "without" markers.

Regards.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• Hi Robert,

I have made a few quick checks:

tested on a dimension of 2000 elts (cube with 5 dimensions): performance is not so bad on consolidated levels (it is rather hard to say cause it depends on the hardware used, I am currently using a local installation, with a virtual machine running)

with markers, perfromance does not seem to significantly increased.

I will try to make some further tests
laloune

Post hoc, non est propter hoc
• Thanks. Good to know.
Robert Tischler
Senior Consultant Vector SW DV GmbH
• # How to Realise Average Calculations in Jedox

As of Jedox 4.0 there is a very efficient way to implement average calculations using the syntax: PALO.DATA.AVG(Server/Database,Cube,Coordinationarray,Expandtypearray). The details for that are explained in the admin manual.

The recursive options (a=a/n) that are proposed above will definitely not work.

Managility Jedox/PALO Specialists in Australia

Check out the new Managility Power Tools for Jedox: managility.com.au/technology/managility-powersync

Jedox Training and Webinars:
Register at: www.Managility.com.au

[IMG:http://managility.com.au/templates/sj_icenter/images/logo.png]
• Hi karaoan,

right the PALO.DATA.AVG are ok but the inconvenient is that they are client-side only. What could really be useful would be to use them directly in rules in order to calculate them in the cube