Percentage Calculation in Framework Manager

[smtest]

Hi,

i am trying to create a percentage calculation in Framework manager but i am not getting the expected results. I dont know what aggregation i suppose to choose, if i select calculated or automatic, it uses xcount by default.

I am trying to create this expression: Total(Terms)/((Total(BC)+Total(EC))/2)

Terms, BC and EC are existing columns in Framework manager.

Thanks

[bus_pass_man]

Is it inexplicably unexpected or is it consistent with an element solve order which isn't what you want?

Have you tried breaking the expression into its parts, determining whether the results for them are what you expect, and then building the expression up gradually?

If you put the expression elements into different query items and building up the expression -- for example putting Total(BC)+Total(EC) into one query item (presumably with the regular aggregate as calculated), then put that query item into another one with the /2 and then putting that in with Total(Terms)/ what do you get?  If you fiddle about with different aggregates you might be able to control the expression to return the results which you want.

[Lynn]

Try using a standalone calculation with this expression where all the underlying elements are set with aggregate method as total:

[Terms] / ( ( [BC]+[EC] ) / 2 )

I would qualify each of these to reference the physical layer rather than the shorthand as I've shown here.

[smtest]

Thanks all for your reply!

if i try [Terms] / ( ( [BC]+[EC] ) / 2 ) with total aggregation, i would get this results:

Term   BC   EC   Turnover
100          200   300   0.4
50          175   300   0.210526316
60          150   300   0.266666667
12          165   300   0.051612903
Total Turnover = 0.9288

as compared to

Term       BC     EC         Turnover
222         690   1200       0.2349

i would like to sum Terms, BC and EC and create a calculation.

thanks

[Lynn]

I can't tell if you didn't understand my earlier reply or if it somehow was problematic for you to implement.

You want the aggregation method for [Terms] / ( ( [BC]+[EC] ) / 2 ) to be calculated, not total.

Set the underlying elements (terms, bc, ec) in your model to total aggregation method. Then create a standalone calculation with aggregation method set to calculated.

[smtest]

hi,
Actually i tried using calculation and still getting wrong results as it uses count. This is what i see

xcount(terms/((BC+EC)/2)

thanks

[MFGF]

hi,
Actually i tried using calculation and still getting wrong results as it uses count. This is what i see

xcount(terms/((BC+EC)/2)

thanks

Looks to me like you did something wrong. Did you create a standalone calculation in your model as Lynn suggested? What did you set the Regular Aggregate property of the calculation to be?

MF.

[smtest]

i created a new column in Business layer with this formula where i have used the fields from the database layer.The aggregation rule is set to be calculated.

[Database Layer].[Terms]/(([Database Layer].[BC]+[Database Layer].[EC])/2)

Thanks

[MFGF]

i created a new column in Business layer with this formula where i have used the fields from the database layer.The aggregation rule is set to be calculated.

[Database Layer].[Terms]/(([Database Layer].[BC]+[Database Layer].[EC])/2)

Thanks

Hi,

So when you say "I created a new column in Business layer", can you be more specific about what you did? How exactly did you do it?

MF.

[smtest]

Right click on Query subject and selected new query item.

thanks

[MFGF]

Right click on Query subject and selected new query item.

thanks

Ok - I think that's your problem. You created an embedded calculation within a query subject. Lynn advised you to create a standalone calculation - there is a difference. Right-click on your Business Layer namespace, select "Create" then choose "Calculation". This will create a standalone calculation outside of any query subject.

MF.

[smtest]

i understand what you mean, but as it creates a calculation outside of query subject, can i move it in the query subject? I want to be part of the same query subject.

thanks

[smtest]

It worked! Thanks for your help!

I am still not sure why it worked as a calculation outside the query subject instead of new query item within the query subject.

[cognostechie]

because the 'calculated' property works only in standalone calculations, not in embedded query items. For any calculation which requires a division operator or if it is a calculation requiring items from two query subjects, the standalone calculation should be used. That way, cognos would generate appropriate SQL. Now coming back to your dilemma as to why it wont work if you would had created an embedded query item and set the property to 'calculated', the answer is that's how FM is made to behave.

[smtest]

Thank You for an explanation!