Hi,
cognos 10.2.2, relational data source.
So, I've got two data items "Sales YTD CY" and "Sales YTD LY"
if ([YTD CY]='Y') then ([Net Sales LC]) else (0)
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":
[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%":
[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.
QuoteBasically, 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 (http://www.cognoise.com/index.php/topic,30396.msg99306.html#msg99306)
Thank you Micheal, indeed it does!