Aggregation problem with new field

[thedon_1]

I have a problem in cognos which involves a new field that was added.

I'm working with a relational model.


I have the following fields for example.


Month, Product, Minimum, Sales, Profit, Registered profit


Basically, if the sales are above the minimum, then show the profit figure multiplied by sales, in registered profit. If sales are not over the minimum then show 0.



The problem is that in the background, the lowest level for this is by week. Therefore when I run my report, it works out if the volume is above the minimum at the WEEK LEVEL, even if the query and report only specify month. It should be doing the calculation at the month level where it aggregates the volumes and then looks at the minimum.


I've messed around with aggregation types but can't get this working. Minimum has been setup as a dimension, should it be a measure? Any idea how to fix this?



Thanks

[blom0344]

In a report use a contextual aggregation like:

total(sales for month) and total(minimum for month)  and compare these.  Be aware that month is probably not unique, so you need to work in the year or calculatie the period  (yyyymm) instead of just the month