Simple calculation won't roll up properly

[mrl72]

Hey all

I have a very frustrating scenario with a simple calculation that when rolled up above anything at the lowest level of the table produces incorrect results. Have not had much luck from IBM support thus far.

Simple calc is:

sum(fact1) / sum(fact2).

Designated as type fact with regular aggregate property set to Calculation.

As mentioned, when grouping on an attribute (like order ID) at the lowest level in the "same" table the calc works fine. But if I then remove that attribute and add one such as "country" the aggregate is wrong. It's close, but not correct. If I drop in a calculated object at the report level which is a simple fact1/fact2 then the roll up for that manual calc is fine.

IBM are telling me I should be adding the calculation in the model by using "Create > Calculation" method. Isn't that for dimensional tables not relational?

Any ideas? Can't believe something so simple is so hard to do in this tool.

BTW, this is the "Cognos SQL" that gets created. The first XSUM is the calc from the package. The 2nd XSUM is the one I manually added in the report. You can clearly see Cognos is applying the aggregation incorrectly in the first one, even though they are identical calculations (with identical aggregate types).

Code Select Expand


       XSUM((sum(total_miles_count) / sum(charge_days_count))  for brand_id )  as  Miles_Per_Day, <--incorrect results
       (XSUM(total_miles_count  for brand_id ) / XSUM(charge_days_count  for brand_id ))  as  Miles_Per_Day_Manual_Calc, <--correct results


Cheers.

[charon]

hi mrl72,

if i understood you correctly, the calculation you are talking about is a calculation made in the fm model, correct?
in this case, try "tota(fact1)/total(fact2)", with the same aggregation type.

regards
charon

[cognostechie]

@ mrl72 - The 'calculated query items' in FM (which look green color) are not just for Dimensional but also for relational. When you
have to do a calculation involving one fact table then you can create a Query Item in the Query subject but when you
have to do a calculation involving 2 or more Fact tables then the calculated item should be used. That's what IBM means.