Re: [birt-dev] Additional aggregation functions

[Henning von Bargen <H.vonBargen@xxxxxxx>]

First of all, thank you for your proposal.

As Claes said, he recently added a DENSE_RANK aggregate function.
So you could take this as an example for how to add additional aggregations.

But I have to admit that I don't fully understand your definitions, and personally I don't think we should add them.

Generally I think we should only add aggregate functions that are available in SQL under the same or similar name.

That's not the case for your definitions.

Furthermore, I think the definitions are quite hard to understand.
And 99,9% of BIRT developers will never need them (or never use them, because they don't understand them).
But OTOH that is also the case for quite a lot of the aggregates BIRT offers now...

I don't want an endless list of aggregate functions which are hardly ever needed, because this confuses people.

And a sum of (w *v) can be calculated easily with the existing aggregate functions,
keeping in mind that the "column" to sum up is actually an expression and can be the product of two columns for example.

So I think these aggregates should not be included into BIRT.
But that's just my personal opinion.

If you want to add them, then at least they should be well documented in the help pages and inside the code,
and their visibility in the UI could possibly be controlled by a system property to avoid confusing users.

There are probably some other SQL aggregate functions which BIRT lacks and which could be helpful for many users.

Kind regards
Henning von Bargen 



-----Ursprüngliche Nachricht-----
Von: birt-dev <birt-dev-bounces@xxxxxxxxxxx> Im Auftrag von Yair Lenga via birt-dev
Gesendet: Freitag, 12. Januar 2024 00:50
An: birt-dev@xxxxxxxxxxx
Cc: Yair Lenga <yair.lenga@xxxxxxxxx>
Betreff: [birt-dev] Additional aggregation functions

Hi,

I am writing to get feedback on extending BIRT aggregation functions with few new functions: weighted average, weighted sum, compound sum. I am using birt to generate various risk reports, and i n found that those functions are essential for many templates. Having them as built-in, will simplify many templates, and will avoid the need to code them repeatedly.

Weighted-sum(v, w, threshold): calculate sum(w*v), ignoring any entry where w it v are null. The result is set to null:
* if any w is null
* if threshold is non-null, and the sum(w ) for entries with v=null / sum(w) > threshold.

The common usage will be to estimate total value of a portfolio, given set of holdings(w), and prices (v). The threshold logic make it possible to avoid showing result unless there is minimal data.

Weighted-average(v, w, threshold). Works the same, taking into account entries with non-null v. Entries with v = null will be excluded from the calculation. sum(v*w)/sum(w), fir all entries with v&w != null. Function return null if:
* any w is null
* sum(v*w) is null
* threshold not null, and sum(w) for v=null / sm(w) > threshold.
* sum(w) == 0

Compound-return(duration, return, threshold)  will calculate compound return base on period returns: product( (1+v) ^ duration ) for all entries with non null duration. Similar to the average/sum, result will be null if
* sum(duration) for non null v is 0
* all v are null
* threshold not null, and sum(duration) for non null v / sum(duration) > thread hold.

Average-return(duration, Return, threshold) - same as weighted average, calculate return per period compound-return(…) ^ (1/sum(duration) for return != 0)

Looking for a feedback.  Have enough knowledge to implement in Java.

Yair.


_______________________________________________
birt-dev mailing list
birt-dev@xxxxxxxxxxx
To unsubscribe from this list, visit https://www.eclipse.org/mailman/listinfo/birt-dev