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

How can I add a summarized Average Rate row under my crosstab?

Started by psrpsrpsr, 04 Jan 2018 03:24:09 PM

Previous topic - Next topic

psrpsrpsr

I have a crosstab that displays an attrition rate for members canceling a service that looks like this:

| Location ID        | 201706 | 201707 | 201708 | 201709 | 201710 | 201711 | 201712 |
|--------------------|--------|--------|--------|--------|--------|--------|--------|
| 12345              | 1.8%   | 1.6%   | 1.5%   | 1.7%   | 1.5%   | 1.6%   | 1.5%   |
| 99876              | 1.9%   | 1.9%   | 2.2%   | 1.9%   | 1.9%   | 1.9%   | 1.7%   |
| Total Average Rate | 1.7%   | 1.8%   | 2.1%   | 2.0%   | 1.8%   | 1.9%   | 1.8%   |   <<< NEED TO ADD THIS


The Total Average Rate calculation for my dataset would be calculated as follows, using the dataset below:
201706: (96 + 218) / (6,000 + 11,964) = 1.7%
201707: (98 + 231) / (6,050 + 11,969) = 1.8%
...and so on.

How can I achieve this? THANKS!


| Location   ID | Year Month   Number | Cancel Count | Subscriber Count | Attrition Rate |
|---------------|---------------------|--------------|------------------|----------------|
| 12345         | 201706              | 96           | 6,000            | 1.6%           |
| 12345         | 201707              | 98           | 6,050            | 1.6%           |
| 12345         | 201708              | 108          | 6,100            | 1.8%           |
| 12345         | 201709              | 99           | 6,150            | 1.6%           |
| 12345         | 201710              | 104          | 6,200            | 1.7%           |
| 12345         | 201711              | 95           | 6,250            | 1.5%           |
| 12345         | 201712              | 109          | 6,300            | 1.7%           |
| 99876         | 201706              | 218          | 11,964           | 1.8%           |
| 99876         | 201707              | 231          | 11,969           | 1.9%           |
| 99876         | 201708              | 270          | 11,976           | 2.3%           |
| 99876         | 201709              | 267          | 12,012           | 2.2%           |
| 99876         | 201710              | 222          | 11,984           | 1.9%           |
| 99876         | 201711              | 251          | 11,978           | 2.1%           |
| 99876         | 201712              | 219          | 12,034           | 1.8%           |

hespora


total(
  [cancel count]
  for [year month number],[loc id]
)
/
total(
  [subscriber count]
  for [year month number],[loc id]
)

would be what goes into the rows, and then just


total(
  [cancel count]
  for [year month number]
)
/
total(
  [subscriber count]
  for [year month number]
)

goes below as a summary line. The trick is here, you do *not* add this as a summary, but at a second measure, putting the measures into rows - one below (logically) loc id, and one on the first level. Without having any data, the attached is how it should look like in RS.

psrpsrpsr

Hespora, you've come through for me again - you rock! However there was an unanticipated interaction with my crosstab - See below:

> My crosstab initially had the [Attrition Rate] nested underneath the [Year Month]. I also had two additional fields: [Attrition Rate - Last 12 mos.], and [ACH %]. These were displaying correctly with the pre-summary row configuration.

> With the attrition rate being nested after [Location ID] rows, the [Attrition Rate - Last 12 mos.], and [ACH %] are no longer displaying correctly. Is that due to the intersection of the two metrics?

> Is there a way to achieve the solution you provided in the last post, but with the [Attrition Rate - Last 12 mos.], and [ACH %] metrics displaying the same way they do in the top example?



Thank you again.

hespora

I gotta admit, I just never tried putting measures in *both* columns and rows. My gut feeling tells me the system is just gonna get confused as to what it's supposed to do at the intersection of two measures. Summaries could indeed do that, but not measures.

If you were to change the definition of attrition rate to "total ( [cancel count] ) / total ( [subscriber count] )" (leaving out the specification of for what level to aggregate), then put that as a default measure and add a summary, that *should* take care of both subtotals over location id and over yearmonth, but that still will leave ACH% unfixed.

Honestly, I do not know how to get what you want.

psrpsrpsr

Figured it out! I simply created a 2-column table, placed two crosstabs next to each other, and chose 'Box Type' = None on the right hand crosstab row headers and intersection cell. Thanks again for your help.