% ot total based on product category

[cognos05]

Hi ,

I have a crosstab like below
                                                       Sales       %of Sales
Product Group   Product Category   

P1                        P1- Knee1                100
                            P1- Knee2                 500

                            P1 -Shoulder1           250
                            P1- Shouler2             300


Now I have a Product team as a seprate column in a table , so I want to divide my sales based on that whole product teams sale

so its something like

Sales of P1-Knee1  divided by sales of ProductTeam='Knee'

Similarly Sales of P1-Shoulder1 divided by sales Product Team='Shoulder'

So i am coming with an expression like

Case when product caetgory in P1-Knee1,P1-Knee2
Then
Sales / Total(sales for ProductTeam=Shoulder' )

Is my denominator the right way to define , it could change base don my where expression .

Thanks,

[CognosPaul]

First, I'm assuming this is a relational source. A dimensional source will require a different solution than what I'm providing below.


Do you have any way of bucketing each Product Category together? P1-Knee1 and P1-Knee2 seems like a subcategories, or maybe a combination of Category and Team.

Either way, your first step here is to get a single node covering both items. Do you have a underlying code for the category? If not, is the pattern always String+[0..1]? Is there always going to be a single number at the end? If so you could do something like:

bucket: substring([Product Category],1,character_length([Product Category])-1)

This should return P1-Knee for both P1-Knee1 and P1-Knee2.

Once you have that you can do almost exactly the way you described before. [Sales] / total([Sales] for [bucket])

Set both aggregate types to calculated and solve order 2.

[cognos05]

Hi Paul,

Yes its relational , so this
bucket: substring([Product Category],1,character_length([Product Category])-1)

Knee should be as a subcategory on the same column where p1 knee and p2 knee will be right?

I dont have Knee as a member as a value in this column , it can be got as a sum of 2 members if it has to be on the same column .

It can be got from a another table where we need to put a filter on product team =10

so which would be the idealy way ,

so for example if i have my crosstab like this
                            Ytdsales  %of sales
Productcategory


My %of sales will have some calc like

sales/ Total (sales  for substring([Product Category],1,character_length([Product Category])-1))

if there is some commonality between my category and total category ?

or assume i have another column which has a common value like knee on it , can i pick that or use that in this calculation.

[CognosPaul]

I would strongly recommend making another data item for the bucket. I suggested the substring only if each category maintained a consistent naming convention. You could also make a crazy case statement. Or push it back to the database, the string "knees" has to be coming from somewhere, and I'm guessing what's hitting the data warehouse is simply a calculated field.

The bottom line is the challenging part for you is figuring out how to make the group. As far as I can see you have the following options.

• Use substring if the string maintains a consistent pattern
• Use a case statement if there aren't too many, but this will make the SQL incredibly complex and long
• Push back to the database. I this is the absolute best and correct solution. The string "knees" in P2-Knees1 must have come from somewhere. Why isn't it in your model?

I'm posting an example report inline. In it I'm recreating your product category by concating product line with country, then creating a product group using substring. Then I'm using two methods to get the percentage. Personally I think percent([Revenue] for [Product group]) is a little easier and cleaner, and works if you have subtotals.

Code Select Expand

<report xmlns="http://developer.cognos.com/schemas/report/14.1/"            expressionLocale="en-us"><drillBehavior/><layouts><layout><reportPages><page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style><pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style><contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1"><crosstabCorner><contents/><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style></crosstabCorner><noDataHandler><contents><block><contents><textItem><dataSource><staticValue>No Data Available</staticValue></dataSource></textItem></contents><style><CSS value="padding:16px;"/></style></block></contents></noDataHandler><style><CSS value="border-collapse:collapse"/><defaultStyles><defaultStyle refStyle="xt"/></defaultStyles></style><crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e2" refDataItem="Product type"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember edgeLocation="e1" refDataItem="Product line"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e3" refDataItem="Revenue"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e4" refDataItem="Total for group"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Total for group"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e5" refDataItem="Revenue / total"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="Revenue / total"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember edgeLocation="e6" refDataItem="percentage"><style><defaultStyles><defaultStyle refStyle="cl"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="percentage"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="cv"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><crosstabIntersections><crosstabIntersection column="e5" row="e2"><style><dataFormat><percentFormat decimalSize="1"/></dataFormat></style></crosstabIntersection><crosstabIntersection column="e6" row="e2"><style><dataFormat><percentFormat decimalSize="1"/></dataFormat></style></crosstabIntersection></crosstabIntersections></crosstab></contents></pageBody></page></reportPages></layout></layouts><queries><query name="Query1"><source><model/></source><selection><dataItem aggregate="none" name="Product line" rollupAggregate="none"><expression>[Sales (query)].[Products].[Product line]</expression><XMLAttributes><XMLAttribute name="RS_dataType" output="no" value="3"/><XMLAttribute name="RS_dataUsage" output="no" value="0"/></XMLAttributes></dataItem><dataItem aggregate="none" name="Product type" rollupAggregate="none"><expression>[Sales (query)].[Products].[Product type] + &apos; - &apos;  + [Sales (query)].[Branch].[Country]</expression><XMLAttributes><XMLAttribute name="RS_dataType" output="no" value="3"/><XMLAttribute name="RS_dataUsage" output="no" value="0"/></XMLAttributes></dataItem><dataItem aggregate="total" name="Revenue"><expression>[Sales (query)].[Sales].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" output="no" value="2"/><XMLAttribute name="RS_dataUsage" output="no" value="2"/></XMLAttributes></dataItem><dataItem name="Category Group"><expression>substring([Product type],1,position(&apos; - &apos;,[Product type]))</expression></dataItem><dataItem name="Total for group"><expression>total([Revenue] for [Category Group])</expression></dataItem><dataItem name="Revenue / total"><expression>[Revenue] / [Total for group]</expression></dataItem><dataItem name="percentage"><expression>percentage([Revenue] for [Category Group])</expression></dataItem></selection></query></queries><XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" output="no" value="true"/><XMLAttribute name="listSeparator" output="no" value=","/><XMLAttribute name="decimalSeparator" output="no" value="."/><XMLAttribute name="RS_upgradeMessageIndex" value="1"/><XMLAttribute name="RS_upgradeMessageIndex" value="2"/><XMLAttribute output="no" name="RS_modelModificationTime" value="2015-11-25T21:38:24.820Z"/></XMLAttributes><modelPath>/content/folder[@name=&apos;Samples&apos;]/folder[@name=&apos;Models&apos;]/package[@name=&apos;GO sales (query)&apos;]/model[@name=&apos;model&apos;]</modelPath></report>

[cognos05]

Hi paul ,

Let me explain  little bit ,
so I have a produc table and then I have a product category table and there is a bridge table between product and product category .

Assume I have a product caegory column

P1-Knee1
P1-Knee2
P1-Shoulder1
P2 -Shoulder2

I can add a value for my bucket which would be
Knee Total
Shoulder Total .


so now how would i define my % of total colum .

can i just say if product category contains Knee then
sales/total(sales within set (kneetotal)

sorry i am writing in dimensional, how would the denominator be here

[CognosPaul]

This is actually an area where dimensional struggles - dynamically creating buckets. Is this DMR or a different source? You really need to push this logic back to the cube somehow. Once that's done, everything will be super easy.

[cognos05]

Sorry i think my last message was not clear. My source is relational with a product, produt category and a bridge table which has product category and product id's  .

I used to write something like this to calcualte % in dimensional reproting .

[Sales]/(Total(sales within set () )

I can have if 's based on captions .

Now i am struggling with realtional to find % of sales.


my categoris ahs values like

P1 -Knee
P2 - Knee

I create a category called Knee Total ( based on your suggestion )

so now i have
                         Sales      %of Growth
P1-Knee
P2 - Knee
P1- Shoulder
P2 - Shoulder

so for % of growth calculations ,

I am not able to relate the P1-Knee, P2-Knee with Knee Total in terms of similar naming .

But i know what i should divide with

so can i write something like a calculation on % of total as


case when  [ProductCategory] like 'Knee'
Then Sales /(total(sales for [Knee Total))

Now here knee total is one of the value of Productategory column .

so i am stuck with above expression on how to get the knee total on the denominator

Let me know if my explantion is clear .