Two different aggregation base on one column with two sources

Question

Hi,

I have question about aggregation in Oracle BI Administration Tool.

I have measure: Numer of policies.

And not this column has two logical source table (with union) from two different products.

In one table it's sum(NUMBER_OF), but in second it's count(distinct POL_NUMBER). Is it possible to implement this in ONE logical column with two source table with different aggregation?

I think that in sql query it must be two different query: 1. With SUM , 2. WITH COUNT and next union all and sum?

Gianni Ceresa · Answer

Aggregation are an attribute of a logical column. If you create the 2 with their needed aggregation and then create a 3rd logical column being derived from the first two (something like col1 + col2) ?

A derived logical column doesn't have a physical mapping, it has a formula based on other logical columns, therefore it could be the sum of your 2 distinct measures that have different aggregation rules.

Just a random guess, doesn't it work?