Help! I have been working on resolving an issue for the past few days and have given up and asking for help.
I have data that contains 2 Members ([Product] and [Selling Company]) and 1 Measure ([Qty Sold]). I would like an output to be a table separates the amount of items sold for each product by Company A from all the other companies selling the product. I then want the table to show (1) the total number of items sold by 'Company A' and all other companies combined, and (2) calculate the % of each product sold by 'Company A' compared to all other companies combined.
Final Output I want:
Company A All Other Companies
Sold % Total Sold % Total
------------------ -------------------
Product 1 20 36.4% 35 63.6%
Product 2 10 20.0% 40 80.0%
Product 3 60 42.9% 80 57.1%
Here are my steps:
1. Created a member called [Not Company A] using a query calculation and the except function to remove company A:
except([All Companies],[Company A]
2. Used a total function to create a new member [Other Companies]:
total(CurrentMeasure within set [Not Company A])
3. Created a calculated set called [AdjustedSellingCompany] that joins the member Company A and the new member Not Company A:
set(member([Company A],'1','Company A'),member([Other Companies],'2','All Other Companies'))
4. Once this is done, I created a calculated measure '% Total':
[Qty Sold]/total([Qty Sold] within set [Qty Sold])
Instead of the report I am hoping for, I get the following:
Company A All Other Companies
Sold % Total Sold % Total
------------------ -------------------
Product 1 20 50.0% 35 50.0%
Product 2 10 50.0% 40 50.0%
Product 3 60 50.0% 80 50.0%
HELP!! What am I doing wrong??? These steps work when I use a set when I drag items directly from the cube. But when I am building my own sets, I am running into problems.
I am working in the Cognos 10 enviornment using Cognos Report Studio with a TM1 cube.
Any help would be much appreciated.
Hi,
First thought - what is [All Companies] - is it a level, a set or a member? It looks suspiciously like it might be the root member of the Companies hierarchy? If so your except() will not work - you would have to get the children of [All Companies] and except [Company A] from this set.
Why are you creating a new set calles AdjustedSellingCompany? Won't this be the same set of members as [All Companies] (or children([All Companies]) if my hunch above was correct)?
Wouldn't your percentage expression be:
[Qty Sold]/total([Qty Sold] within set [AdjustedSellingCompany])
(or within set [All Companies] (or children([All Companies])) depending on the above)?
MF.
Outstanding. I was making this way more complicated then necessary!