If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Nested Crosstab Percent Calculation

Started by Poochi62, 24 Jun 2013 12:15:37 PM

Previous topic - Next topic

Poochi62

I am trying to get a percentage calculation in my crosstab and cannot figure out the formula.  I have Products and then a Yes/No variable nested on the rows.  I have Dates going across and I have Quantity and Percent in the columns.  Here is what I want:

                                        JAN  %           FEB  %                MAR
                                        -----                -----                    -----
Product 1 Qty      YES        120   60%      150  75%        160   80%
                           NO          80   40%         50  25%          40   20%

Product 2 Qty      YES        100   71%      110  65%        150   79%
                           NO           40   29%        60  35%          40   21%

Totals                  YES        220   65%       260  70%        310  79%
                            NO        120   35%       110  30%          80  21%

I am using the following Formula for the Percentage:  percentage ( [Qty] at [YES/NO Flag] for [Month Date] ) but it is not calculating correctly at all.  The results have the same percentages repeated throughout the crosstab.  See attachment.

I have set the solve order for the Percentage to 3 and changed the Aggregate and Rollup to 'Calculated' to no avail.  This is a Relational source, not a Cube-base sourced report.  Any ideas?