garbage value in calculation fields/find average of calculated fields

[kiran.timsina]

My data in DB is spread along dim1/dim2/dim3. I have made a new query subject with new metrics(M1,M2) which spread along dim1/dim2. If I make a third metric whose formula is M1-M2, it works. But if I make a fourth metric whose formula is M1/M2, it gives me garbage value.

For now, I am solving the problem by making the fourth metric in Report Studio. But I wanna make the metric available in Analysis Studio aswell. What do I need to do to make the metric right in FM so that it is available everywhere?

A small problem but crazy :(.

[MFGF]

Remember that values are aggregated in Cognos 10. What is the timing of your fourth metric - are the underlying values being aggregated first, then your division applied, or is the division being calculated for each underlying row then the results aggregated?

MF.

[kiran.timsina]

The underlying values are being aggregated first, then division is applied.

[kiran.timsina]

I also tried changing the Aggregate type for the metrics but none work. But I think it should be sum.

Let me make more clear of my situation. I have sales at item/location/day level. Metrics M1 and M2 are week-to-date sales and week-to-date cost price. M3 is gross margin which is week-to-date sales MINUS week-to-date cost. M4 is %gross margin i.e. (week-to-date sales- cost /cost)*100%.

But finally I view the report at department/location level. So the report will be as
dept/loc/wtd sls rtl/wtd sls cst/wtd gm/wtd %gm.

[blom0344]

I think that the issue is with the addition. When calculating  A + B it does not matter if you aggregate first and then perform the addition, or perform the addition of the aggregates. Calculating gross margin demands perform the aggregate first (to the required level) and then the final division.  I think this will only work when you hardcode the aggregate within a context:

((total(wtd_sales-cost) for somelevel) / (total(cost) for somelevel) )*100

I never define calculations within models, so this is just my 2 cents..

[kiran.timsina]

If we only pull item/location from a table which is at item/location/day, Report Studio groups by the result to item/location. Can't we do such in FM? According to blom0344, we need to do the aggregation out of FM, may be in a view. So, will it be correct to conclude that in my case I can't solve the case inside FM?

[blom0344]

Oh boy, you are not getting my message. Adding a context is done WITHIN  FM ..

The point is that you need to explicitly aggregate first and then do the division..

[kiran.timsina]

blom0344! Sorry for almost a wrong conclusion  :-[  Can you tell me how to do explicit aggregate in FM? I've been referencing the redbook on FM but could not find any clue to do it.

And thanks for all those quick replies.

[blom0344]

In the calculation GUI select an aggregate like  total.  There is help displayed in the tips panel like:
total ( [ distinct ] expr for [ all | any ] expr { ; expr } )
From the help: For  Works with summary expressions to define the scope of the aggregation in the query.

[kiran.timsina]

I tried using total expression as you said, but still it seems not to work. I made
- week-to-date sales  metric as:  total(case when sales.day_key between week_st_dt and week_end_dt then sales_rtl else 0 end for sku_key, loc_key)
- week-to-date cost metric as:  total(case when sales.day_key between week_st_dt and week_end_dt then sales_cost else 0 end for sku_key, loc_key)
- i made a third metric in the same query subject just to test divide as :week-to-date sales/week-to-date cost

Anything wrong with this?

[blom0344]

Testing it in FM may be a bit difficult..
I took one of my models and created similar calculations.  One numerator, One denominator,  one calculation that defines the division.
Taking a fact table and totalizing the finely grained data to a high level  with the  'for' context.
Published and used the calculations within Query Studio.  This works like expected.
Reporting at the level of the context I get the the proper calculated values..
Reporting at a lower level causes repeats of the data as the context is still valid.
The definitions were defined within calculation items, not within the query subject where they originate from..

[kiran.timsina]

I tried it again. Actually, the metric is working fine at sku/location level.  8) But when I define such calculation in DMR package, i should be able to use the metric for higher levels like dept/location or class/location. But I get garbage value if I make a report at higher level using regular dimension and measure dimension. And if I make relational report at higher level, it groups by sku/loc which is the level at which the calculation is defined. So, instead of aggregating the data, I get lowest level data. I tried changing the regular aggregate to average, sum, etc but it doesn't help.

At the end of the day, the new problem is- how can we define such calculation so that aggregation can be done on the fly for higher levels in DMR package. For now, I am making such calculations in report studio. But the same metric also has to be available in cube reports. :(

[MFGF]

Try defining your calculation as a standalone calculation in a folder (ie not embedded within your query subject initially) and set the RegularAggregate property to Calculated. Add it to your Measure Dimension and see if this gives you desired results.

Regards,

MF.

[kiran.timsina]

MFGF, I was doing as you said. I am still stuck. And a new thing I observed is that once I define calculation metrics, the report takes in average 4 times longer to run and that also with wrong calculation. (I am making test report at dept/location level with the new measures.)

[kiran.timsina]

The problem is solved. I might have been doing something silly before. Thanks guys.