Author Topic: Counting, by Definition of Entry, in a List Column  (Read 281 times)

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Counting, by Definition of Entry, in a List Column
« on: 19 Feb 2018 11:05:30 am »
Am learning Charts/ Visualizations and studying Pie and Vertical Bar for Pareto.

We have a Column, 'Cause of Incident', which can have 34 different entries.  As far
as I know, Cognos does NOT have an Excel-type function of COUNTIF.

I Googled trying to find the maximum number of Columns in a List.  Did not find
that, but 2 people said they have successfully had 54, and 74, columns in a List.

I know that by using CASE WHEN, I can Count the number of occurrences of a
particular entry in a Column.  So ... I can add 34 columns to get Counts for each
type of entry.

Is there a better way to Count these 34 different entries?

Plus, I am so novice in Charts (I have a Vertical Bar Chart in an Active Report).
Would there be a problem using the "Totals" (Counts) line as Series for a Vertical
Bar Chart?

I am not sure if a Pie Chart can display 34 different slices, and if it could, I don't
think it would be very useful ... MUCH better to Pareto up to 34 different entries
in a Vertical Bar.  For years in Access 2003 automation to Excel 2003, I wrote very
useful Pareto Vertical Bar Charts w/ 30 different entries.

Thoughts?  TIA, Bob

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Counting, by Definition of Entry, in a List Column
« Reply #1 on: 20 Feb 2018 03:15:11 pm »
Playing w/ this, and thinking Multiple Series might work in a 100% Stacked Bar Chart.

Of the 34 possible definitions of "Cause of Incident', I have written Case When for
5 of those 34 definitions.  They all Total/ Count correctly.  Those are in a Totals Line
in the List ... not sure the Chart will place those numbers in the Stack?

I then work w/ a 100% Stacked Bar Chart, and have no problems assigning those 5 definitions,
but "No Data Available" when the Report w/ the Chart is run.

Here's where I don't know what to do ... besides wondering how to display the Counts
in the Totals Line of the List.

There are still 2 "Objects" to complete ...
1 - Default measure (y-axis)
2 - Categories (x-axis) ... are the "Categories" the Query definitions written for the 5 example definitions?

I did assign 'Cause of Incident" to the Categories (x-axis), but Nothing assigned for the Default measure (y-axis).

Thoughts?  TIA, Bob

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Counting, by Definition of Entry, in a List Column
« Reply #2 on: 20 Feb 2018 07:49:27 pm »
With the many Cognos Visualizations, I am wondering (and hoping), is there one to take a Column such as ...

[Cause of Incident]
AAA
BBB
CCC
BBB
CCC
CCC

... and do something like a Pivot Table
result to build a Visualization which
would display (in some way) ...
CCC = 3 ... 50%
BBB = 2 ... 33.3%
AAA = 1 ... 16.7%

If such a Visualization does not exist,
can this be done?

TIA, Bob

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Counting, by Definition of Entry, in a List Column
« Reply #3 on: 21 Feb 2018 02:00:22 pm »
Still trying to get a Query/ List design for a Chart ... Pie, Bar, Pareto, whatever will work.

Am attaching Excel file which hopefully illustrates the problem.

IF we can assign the Counts w/ the “Cause of Incident” to a Pie, then the Pie would
calculate the percentages.  I deliberately left in ALL row entries for “Podunk” because
“Smithtown” might appear in future Reports.

Thoughts please.  Thank you, Bob

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Counting, by Definition of Entry, in a List Column
« Reply #4 on: 23 Feb 2018 01:09:21 pm »
SOLUTION ... Utilizes a Pie Chart where Footer for 'Cause of Incident' had for the Column 'Cause of Incident' ...
Detail Aggregation = Default
Summary Aggregation = Default
 … and …
'Incident Internal ID' …
Detail Aggregation = Default
Summary Aggregation = Default

Had a Query Calculation of 'TestCount' for the Footer value in the Column 'Incident Internal ID'.
 'TestCount' = Count ([Incident Internal ID] For [Incident Location], [Cause Of Incident])

Chose not to display Legend as Two Different Pies showed ALL Legend answers for the two
Locations even if a Location did NOT have that 'Cause of Incident'

 


       
Twittear