Cognos Experts!
I'm facing an interesting challenge and can't seem to come up with a solution. I have a simple dimension with Products that roll up 2 levels to a Market. For any given month I'm trying to calculate Market Share with a shared measure for Sales.
From a relational package, the user selects a Product from a value prompt which filters a Product specific query with Sales. This query is joined to a Market query (on Market and other common dimensions) which automatically filters this Market query for the Products Market also aggregating Sales. The join query then calculates Market Share by dividing Product Sales from the Product Query over Market Sales from the Market Query.
From a dimensional source (a Transformer cube not DMR) this isn't as straightforward and Joining Query Subjects results in an error (GEN-ERR-0015) about the function 'realDataMode' not supported. I've tried doing this with a UNION but can't figure out how to filter the Market query on the selected Product's Market.
Any thoughts, advice, tips?
Thanks,
Kado
You would do this in one simple query using a dimensional package. You get your product sales value by using a query calculation using a tuple of the product and the sales measure, and you get your market sales value by using a query calculation using a tuple of the market and the sales measure. You can then use these in your calculation for market share.
Cheers!
MF.
Thanks MF!
I like the sound of your solution but am having trouble creating a Tuple for Product and a Tuple for Market as they reside in the same hierarchy. By using the Insertable Object 'Intersection (Tuple)' it only allows me to select the same hierarchy which is called PRODUCTS (which goes from Market, to sub-Market, to Product).
Just using a data item and writing a Tuple function I couldn't seem to get it to work either. I tried something like:
tuple([Market A], [Sales])
tuple([Product A], [Sales])
But Cognos didn't like these.
Any additional thoughts!
Thanks!!!!
Kado
Any thoughts on this MF???
thanks,
Kado
Your prompt should return a member from your Product level, so you can calculate the market share as:
tuple( ?pProduct?, [Sales] ) / tuple( ancestor( ?pProduct?, [Product Hierarchy].[Market Level] ), [Sales] )
This is assuming that [Sales] is a member of the measure dimension. The ancestor function chooses the member of the Market level to which the selected Product rolls up.
Hope that helps.
Nice!!! I hadn't thought about referencing the ?Par? in the tuple and that worked!!!
;D