Trouble with aggregates on calculations

[hespora]

Hi,


cognos 10.2.2, relational data source.

So, I've got two data items "Sales YTD CY" and "Sales YTD LY"

Code Select Expand

if ([YTD CY]='Y') then ([Net Sales LC]) else (0)

Code Select Expand

if ([YTD LY]='Y') then ([Net Sales LC]) else (0)

(YTD CY and YTD LY are boolean flags set into dimennsions provided by the Package, Net Sales LC is a plain fact)
The aggregate function for these is set to "Total", the rollup aggregate function is set to "None". Works just fine.

Now, I have another data item "Sales Growth YTD":

Code Select Expand

[Sales YTD CY] - [Sales YTD LY]

I can set aggregate function on this to either calculated or total, with the rollup aggregate function at none. Both works just fine.

Now what I need is a fourth data item "Sales Growth YTD%":

Code Select Expand

[Sales Growth YTD] / [Sales YTD LY]

Ignoring the lack of capturing a div/0, this one doesn't work as apparently, when I go down to no level of aggregation, my initial data items are never both filled; it's always a zero value for one of them (makes sense, as the lowest level of detail I have in this data source is the individual billing document, which is set at one point in time, so it's always either CY or LY).
Basically, what I need is for this data item to do the calcluation *after* the other data items have been calculated and aggregated. Is there any way to get this to work within one query? I know nested queries should be an option, but I'd like to avoid that.


Also, does anyone have a good read on aggregate functions and rollup aggregate functions they can suggest? At times, I really feel kinda "lost" with those.

[Michael75]

Basically, what I need is for this data item to do the calcluation *after* the other data items have been calculated and aggregated. Is there any way to get this to work within one query?

I've a feeling that this reply from MF could help you to get your calculations performed in the right order:

http://www.cognoise.com/index.php/topic,30396.msg99306.html#msg99306

[hespora]

Thank you Micheal, indeed it does!