percentage in crosstab

[Irene]

Hi


I am trying to create the following crosstab report:

Column : Status
Row     : Officer Name
Measure : Number of cases
               

                 | open                        | Close                     | Total |
                 | No of cases | %         | No of cases | %      |         |
                  ------------- ---------  ------------  -------  ------
Officer 1      | 2               | 20%      | 8               | 80%   | 10     |
Officer 2      | 4               | 20%      | 16              | 80%   | 20     |
                   ------------  ---------  ------------- -------  ------
Total           | 6               | 20%      |24               | 80%   | 30     |

How do I get the figure of the percentage marked in red. (total row, % column)?

When I use the aggregate function, it totals up the % of officer 1 & 2 making it 40%.

It should be 6 / 30 = 20%.

The % column's expression = [No of cases] / total ([no of cases]) for Officer)

Please help

[blom0344]

Did you try to set the aggregate behavior to : 'calculated' ?  (instead of total)

[Irene]

Changed the aggregate behaviour of the total field to calculated as per your suggestion. I still don't get required %.

The expression for the total is ........total(currentMeasure within detail [Officer])

The % column's expression is what I mentioned before i.e [No of cases] / total [No of cases] for [Officer])

Any other suggestions?

I also tried to set the 'Define Contents' to yes and insert my own expression into the total % cell but the value doesn't appear.

[blom0344]

total([No of cases]) for Status / total ([no of cases]) for Report)?

A guess..

[k2]

Hi,
I guess,this may help you.

[% column] / total([% column] within set [office1],[office2]).

Cheers

[blom0344]

Hi,
I guess,this may help you.

[% column] / total([% column] within set [office1],[office2]).

Cheers

What happens when there are more than 2 officers? Doesn't look plausible to me..

[rks]

You should set property "Solve Order" for column "Status" and row "Total".

http://support.cognos.com/opendocs/en/html/cognos8_bi/8.3/ug_rptstd_fin_id2078CalculationSolveOrder.html

           

[Irene]

could you elaborate on how I can use the solve order. Thanks in advance

[rks]

I see three calculated Data Items
Right calculation order for cross tab cells is:
1 - Total ([no of cases])
2 - Total ([Officer])
3 - % ([no of cases])

Try set any value property Solve Order for query data item "% ([no of cases])" (for example: 100), others leave blank
or
Set property Solve Order for each data items
1 - Total ([no of cases]) (Solver Order: 1)
2 - Total ([Officer]) (Solver Order: 2)
3 - % ([no of cases]) (Solver Order: 3)

It should work ..

[bonniehsueh]

Column : Status
Row     : Officer Name
Measure : Number of cases
               

                 | open                        | Close                     | Total |
                 | No of cases | %         | No of cases | %      |         |
                  ------------- ---------  ------------  -------  ------
Officer 1      | 2               | 20%      | 8               | 80%   | 10     |
Officer 2      | 4               | 20%      | 16              | 80%   | 20     |
                   ------------  ---------  ------------- -------  ------
Total           | 6               | 20%      |24               | 80%   | 30     |

I agree with rks. Change the Solve Order for the first percentage column to 998. The second % column to 999. This should ensure that these columns are calculated after everything else.

If that doesn't work, change the solve order for the Total Column to 997 so that it forces it to calculate before the columns you already defined.

You can change the numbers later, but main idea is that it should get the %'s to calculate after the Totals.

Solve Order can be set in the properties pane of the calculated data item under the Data Item section.

Let us know if this works.

[srini_in72]

Hi,
Adding subtotal row with aggregate function would take care of calculating percentage in crosstab. It worked for me.Even you can try by setting solve order as well.

[bonniehsueh]

Hi,
Adding subtotal row with aggregate function would take care of calculating percentage in crosstab. It worked for me.Even you can try by setting solve order as well.

Can you give an example of this Srini_in72?

[ComplexQry]

Is this datasource a cube or a relational database?  Because if it is a relational database, having the roll-up aggregation set to Calculation worked for me.  BUT, I cannot get averages to work if they are sourced from a cube.

[Irene]

Hi

The solve order works somewhat but it poses another interesting challenge:
                                        <status>
                 | open                        | Close                     | Total B |
<Officer >  | No of cases | %         | No of cases | %      |            |
                  ------------- ---------  ------------  -------  ------
Officer 1      | 2               | 20%      | 8               | 80%   | 10       |
Officer 2      | 4               | 20%      | 16              | 80%   | 20       |
Officer 3      |                  |             | 10              |100%  |10        |
Officer 4      |10               |100%      |                  |         |10        |         
                   ------------  ---------  ------------- -------  ------
Total  A       | 16              | 40%      |34               | 85%   | 50       |


The Officer 3 Open column is blank and the officer 4 Close column is blank as there are 0 records.

When I applied the solve order:

% column = 100
No of cases = 99
Total B = 98, this is what happened.

The Total A % for Open= 16 / 40 (instead of 50). It only took the total of those rows which are not blank.

Same thing for Total A % for Close = 34 / 40 (instead of 50).

The % columns has the formula = [no of cases] / total([no of cases]) for [Officer]. The

Total A's formula = total(currentMeasure within detail[Officer] )
and Total B's formula = total(currentMeasure within detail [status])

We are almost solving this? Any ideas?

[rks]

When I applied the solve order:

% column = 100
No of cases = 99
Total B = 98, this is what happened.

I'm sorry.
I was wrong about calculation order.
Try:
1) Total A (Total [Officer])          - Solve Order: 98
2) Total B (Total [No of cases])   - Solve Order: 99
3) % column                             - Solve Order: 100 

[bonniehsueh]

I would agree with rks's revised solve order. Also if you are having issues with the blank cells, those cells are probably coming up with null values which impacts calculations that use those cells. To resolve this, I would suggest forcing 0's in those columns. If ([Close no of cases] < 0) then (0).

Not sure what version your using but in 8.2 nulls were treated as 0's but with 8.3, nulls are treated as nulls. This is noted in the 8.3 Product Behavior changes documentation on the KB.

[Irene]

I tried the revised solve order....still didn't work  :(

I managed to allow 0 to appear instead of blank by changing the data format to numeric and setting the 'missing value characters' to 0.

I'm wondering...could it be one of my formulas is the issue here.

The % columns has the formula = [no of cases] / total([no of cases]) for [Officer]. The

Total A's formula = total(currentMeasure within detail[Officer] )
and Total B's formula = total(currentMeasure within detail [status])

[Irene]

Or is there any way I can insert another calculation into the cell after setting the define content = Yes

[pirx]

Hey,

do you have finally for this problem?
I got the quiet similar issues.
Thanks,
P