nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Additional Average Functions: PRODUCT, GEOMEAN

YairLenga

Currently, BIRT has large number of aggregation functions, but non of them can handle calculations involving compounding - (geometric averages).

Does anyone know how much effort it will be to add the following Excel-like aggregations:

PRODUCT(x) - Multiplication of all the values X(1)*X(2)*X(3) of a series.
GEOMEAN(x) - Geometric mean, simply calculation as GEOMEAN(X) = PRODUCT(X)^(1/COUNT(X))

Implementing those calculation as a built-in function will simplify reporting for time-series data. In many cases, historical data is collected on a daily basis, but need to be reported on monthly, weekly, etc. With the new functions it will be simply monthly_total = PRODUCT(1+daily)-1 - which will include the compounding effect, whereas the AVERAGE(daily) does not include compounding effect.

Using the excel-like names and behavior will make it them much easier to locate and use.

Find more posts tagged with

Comments

kclark

Do you have some sample data you could provide? I think you might be able to do something like that with a computed column.

YairLenga

<blockquote class='ipsBlockquote' data-author="'kclark'" data-cid="112158" data-time="1354833921" data-date="06 December 2012 - 03:45 PM">
Do you have some sample data you could provide? I think you might be able to do something like that with a computed column. </blockquote>
 
Hi, 
 
Our current implementation is based on compute columns. However, this is far from ideal. Every request for compounded columns need to be implemented with two compute columns - one for compounding, and one for calculating the display value. This translate to lot of debugging time and frustration. 
 
For example, instead of saying Aggregation=GEOMEAN, expression = (1+r/100). One has to write: 
 
(compute column) V1 = aggregation=SUM, expression=log(1+r/100) 
and then, RESULT= V1^(1/count(N)) 
 
The procedural implementation (JavaScript) is even more difficult to maintain, as it requires global variables. etc.

Hans_vd

<blockquote class='ipsBlockquote' >Does anyone know how much effort it will be to add the following Excel-like aggregations</blockquote>
It's not that difficult. 
 
With hardly any experience in java programming and based on nothing more than <a class='bbc_url' href='http://www.ibm.com/developerworks/opensource/library/os-eclipse-birtextpts/index.html'>this article</a> and <a class='bbc_url' href='http://www.informit.com/articles/article.aspx?p=1748986&seqNum=4'>this article</a> I managed to create <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1563-birt-group-functions-plugin/'>this devshare submission</a>.

YairLenga

<blockquote class='ipsBlockquote' data-author="'Hans_vd'" data-cid="112217" data-time="1354913180" data-date="07 December 2012 - 01:46 PM">
It's not that difficult. 
 
With hardly any experience in java programming and based on nothing more than <a class='bbc_url' href='http://www.ibm.com/developerworks/opensource/library/os-eclipse-birtextpts/index.html'>this article</a> and <a class='bbc_url' href='http://www.informit.com/articles/article.aspx?p=1748986&seqNum=4'>this article</a> I managed to create <a class='bbc_url' href='http://www.birt-exchange.org/org/devshare/designing-birt-reports/1563-birt-group-functions-plugin/'>this devshare submission</a>. </blockquote>
 
Hans, 
 
Thanks for looking into this. I've looked at the article links that you posted. Seems straight forward. 
I'll try the plugin early next week. 
 
Do you know how can to get those enhancements into the official BIRT build ?

Hans_vd

Hi YairLenga,

Just to make sure: the plugin I created will no solve your problem. I was just trying to say that it isn't too difficult to create your own aggregation extension.