Aggregation order

[RudiHendrix]

In FM I have a calculated measure (Price). This is calculated as invoice_amount / invoice_volume.

Now the data is as follows.
Invoice   Invoice_item    Invoice_Amount       Invoice_Volume         Invoice_Price
10000     A                   1000                     4000                        0,25
10000     B                   1000
10000     C                   50

If I pull up a report displaying this data everything is being calculated correctly. However if I pull up a report displaying only the Invoice data it displays the following:
Invoice   Invoice_Amount       Invoice_Volume         Invoice_Price
10000     2050                     4000                        0,25
Obviously the price should be 0,5125.

How and where can I set the aggregation properties to have this value be calculated correctly?

[blom0344]

If you want to perform divisions with aggregates, then you need both components as dataitems:

A and B

Invoice price is than stored as dataitem C  (as A/B ) with aggregation set to 'calculated'

This way the division is performed AFTER aggregation..

[RudiHendrix]

Perhaps I'm not understanding the solution correctly or I'm not making clear what the issue is.

If I display a report as such:
Invoice   Invoice_item    Invoice_Amount       Invoice_Volume         Invoice_Price
10000     A                   1000                     4000                        0,25
10000     B                   1000
10000     C                   50

It is correct. Because that IS the Invoice_price for that specific Invoice_item for that specific Invoice.

However if I display a report like following:
Invoice   Invoice_Amount       Invoice_Volume         Invoice_Price
10000     2050                     4000                        ?

The price should be 0,5125 and not 0,25. Currently still 0,25 is displayed. But 0,25 is not the price for Invoice "10000".

I've tried setting the regular aggregate to "Calculated", but still I see the same result :(

[RudiHendrix]

I've found some info here:
http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_fm.8.4.0.doc/ug_fm_id7982usage_aggregation_properties.html

In this example:
For example, to divide debt by credit for each row, the SQL looks like this:

Select
customer, debt, credit, debt/credit as
percent_debt from x
To aggregate for all customers, the SQL looks like this:

Select sum(debt), sum(credit), sum(debt)/sum(credit)
as percent_debt from (Select customer, debt, credit from x)

The latter one is exactly what I want! However... a little bit before that passage in the documentation I can read:
"The calculated aggregation type is not supported for calculations that are embedded within query subjects. "

I can't believe that really  is the case! There must be some way to do these calculations before it ends up with the QS user! Is there no way?

Edit:
I should read a little better:
It also says the following:
The calculated aggregation type is supported only for the following:
-stand-alone calculations
-calculations that are embedded within measure dimensions and are based on measures from the same measure dimension

I think this is exactly what I'm doing here! So, I'm really wondering why it still isn't working!

[blom0344]

Do not dispair. I have at least 1 report containing tens of calculations the way you describe them. The trick is to define  A as counter with aggregate as total and B as denominator with aggregate as total and then define C as  A/B with aggregate as calculated.

Obviously, do not fetch Invoice_item to allow for the proper aggregates!!

[RudiHendrix]

If I create the calculation in QS or RS it does work. But of course I would like to do the calculation in FM and present it as an item that the user can select and drag to the QS working area. If I do that it doesn't work.

I agree, dat I shouldn't fetch Invoice_item. Because then it will definately not aggregate (and why should it? :))

I have made some screenshots that I would like to add. How can I attach files to a thread?

[blom0344]

In FM you can enforce it by defining the aggregates in an inline view. That way you are enforcing Cognos to aggregate first and perform the division later. Unfortunately this is not flexible as the aggregate is a static expression. It will also force you to store the definition as an SQL query subject instead of a model query subject.

[RudiHendrix]

That sounds very interesting!

Could you please elaborate a little bit on this solution?

How can I define the aggregates in an inline view in FM?

As I understand I should create a SQL Query subject. But that way the query subject will be a separate item that is not connected to dimensions.
Because a separate SQL query subject would then be something like:
select (sum(amount)/sum(volume)) as price from t_f_billing

And this:
select id_pk, invoice_dimension_id, (sum(amount)/sum(volume)) as price from t_f_billing
will not work.

So that separate entity is just there...or am I misunderstanding this one again?

[RudiHendrix]

In an E-mail (as response on the screen shots that I've sent) blom0344 has pointed me in the right direction!

Let me share the final solution with you.

The Query Subject Invoice_price in the end should not be calculated as follows:
[Business view].[Factureringfeiten].[Gefactureerd Bedrag in Euro's]  / [Business view].[Factureringfeiten].[Gefactureerd Volume MJ]

But as follows:
total([Business view].[Factureringfeiten].[Gefactureerd Bedrag in Euro's])   / total([Business view].[Factureringfeiten].[Gefactureerd Volume MJ])

This needs to be done, because in standard methematics divisions are done before aggregations. Still, leave the Regular Aggregate of the Query Subject to "Calculated" and it works out fine!

As always: in the end the solution is fairly simple! :)

Many thanks go out to blom0344!