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

Percentage for each Measure in CrossTab - See Attachment

Started by rahulp, 13 Mar 2014 03:32:52 PM

Previous topic - Next topic

rahulp

Hello again,

Thanks to this awesome website, I was able to find a lot of useful information that have assisted me in just 2 days of browsing through.

Anyways, there is this one hurdle that I am still having a Hard time accomplishing. This is what I have:

A simple query with 4 fields:
1. Facility Name is the Row Header
2. Month (created using _first_of_month function in query) is the Top Column Header
3. Visit Status is the Secondary Column Header
4. Visit Count is the Measure.

This is what I have accomplished using this site and my own digging around:
1. Percent for the total of each Column Header and Row Headers.

This is what I am needing:
1. A percentage for each column. Implying: in row 1, I have Facility Name as Main, this is for the month of January (just say), and the 2 types of Visit Status are Checked in and Cancelled. There is a summary function that totals them to a number. So if Checked in were 15 and cancelled was 5, the summary column shows 20.

What I want is to have a Percent after each Status. Thus, instead of the 3 columns (Checked in, Cancelled and Total), I want 5 columns: Checked In, Checked In Percent, Cancelled, Cancelled Percent and Total.

Checked In percent would be 15/20 would be 75% and Cancelled percent would be 5/20, thus 25%.

I am attaching something I found on IBM sight, which is exactly what I am trying to obtain. However, the only calculation I am able to add is Query Calculation which does not help.

I have tried [Visit Count]/total(currentMeasure within Detail [Visit Status]) as well as [Facility Name] instead of Visit Status.

I even put the solve order to Last (10) and also tried messing around with Aggregate and Rollup Aggregate, but all in Vain.

Need help.