Percentage Calculation

[Nagesh]

Hi,

My environment is Cognos 10.1.1. and Created a Crosstab report using Cube as data source.

I am facing issue for percentage calculation. Please see attached image.

Row section is coming from different Dimensions

First column is from Time dimension using filter function to get only Jan data.
The other columns Col1 and Col2 are from another dimension ( not all categories). 

Totals is OK.

Unable to achieve Percentage Calculation.  Percentage calculation for col1 and Col2, Col3 are based on first Column, Jan. 

eg. Percentage Calculation for Col1 = Total (Col 1) *100/Total (Jan)

Any ideas ?

Thanks in advance.

[MFGF]

Hi,

My environment is Cognos 10.1.1. and Created a Crosstab report using Cube as data source.

I am facing issue for percentage calculation. Please see attached image.

Row section is coming from different Dimensions

First column is from Time dimension using filter function to get only Jan data.
The other columns Col1 and Col2 are from another dimension ( not all categories). 

Totals is OK.

Unable to achieve Percentage Calculation.  Percentage calculation for col1 and Col2, Col3 are based on first Column, Jan. 

eg. Percentage Calculation for Col1 = Total (Col 1) *100/Total (Jan)

Any ideas ?

Thanks in advance.

Hi,

1. Using a filter function seems very expensive here. With a dimensional package, you have access to members directly. Remove the calculated item for Jan derived using a filter() function, and simply drag in the Jan member from the member tree.

Can you explain what you mean by "not all categories"? Are you saying some of the columns are attributes? If so, you're giving yourself huge problems.

If all the items in the crosstab are either members or meaures, you can use the completeTuple() function to derive the measure values at each intersection, but as we don't know exactly what's in the report it's difficult to say for sure.

Cheers!

MF.

[Nagesh]

Thank you for suggestion. I will try that.
All columns are members of same dimension. But first column should restrict to Jan month and the other 2 columns data should be YTD. That is the reason i am using filter function for first column. Any further suggestions ?

Regards

[MFGF]

Thank you for suggestion. I will try that.
All columns are members of same dimension. But first column should restrict to Jan month and the other 2 columns data should be YTD. That is the reason i am using filter function for first column. Any further suggestions ?

Regards

How are you calculating your YTD values? Is it done in the cube, or are you using calculations in your report?

MF.

[Nagesh]

Thank you.

I struck with personal work and not able to concentrate on this.

I tried all possible ways with my limited knowledge... I am not able to achieve the percentage calculation.
Regarding YTD.. I am not using any special .. it is auto summary of row and Column for a given year.

I even tried with averages instead of fixing month. user is fine either fixing Month or Average. More suitable is average value.

                Avg of Col1    Col2          Col3            Col2+Col3
Dept A           30              2              3                     5
Dept B           25              1              2                     3
Total              55              3              5                     8
%                 100       100*3/55    100*5/55        100*8/55

All Col1,Col2,Col3 are members of a Dimension.

Avg of Col1 is calculated Item Total([Default Measure] within set Col1)/7.  7 is the number of months in current year.  I want to make this dynamic,  for next month for the current year it should be 8. if user selects previous year it should be 12. with solve order 4

% is Calculated Item as _format([Total]/Total( measure within set [Dept],[Avg of Col1]),PERCENTAGE_1) with solve order 2

with % calculated item, the report become slow.

Any suggestions to make dynamic denominator for Avg Col1 ?

Any other ideas to achieve this type of Percentages ?


Regards,