Countif Expression Report Help (Simple)

[t0mato]

Hello!

I need some help writing a basic COUNTIF calculation for a report.

Assume the following Data Fields:
[Division] = Assume inputs A-Z (e.g., "A", "B", "C", etc.)
[Job Family] = Inputs: 'Store', 'Warehouse', 'Corporate'
[Requisition #] = A unique number, identifies opportunity in which the candidate is associated

The report request is to detail, by [Division] how many candidates are in the [Job Family] 'Store' or 'Warehouse' with separate columns.

Example

[Division] [# of Store Candidates] [# of Warehouse Candidates]
     A                    15                                  5
     B                    50                                  21
     C                    12                                  9

My question is: What would the query expressions be for [# of Store Candidates] and [# of Warehouse Candidates]?

I have tried the following for [# of Store Candidates]:

Code Select Expand

IF ([Job Family]='Store')
THEN (
count(
[Requisition Number]
for
[Division]
)
)
ELSE (0)

In the presentation layer, with just [Division] and the [# of Store Candidates] expression, the calculation appears to work correctly, however, the [Division Column] repeats some divisions and returns 0 for the calculated field in those instances (i think these are due to the ELSE 0 in the statement).

What adjustments need to be made here for this to exclude ELSE argument completely? Perhaps this isn't the correct expression altogether. Any help would be greatly appreciated.

Thanks!


   

[JCarter]

My first thought when reading that, is that you're making it too complex. Just use the three data fields you defined, create a crosstab with Division on the left, Job Family on the top, and Req# as the measure with "Count" as the aggregation. Then let Cognos do the work.

Another option would be to create a field for each Job Family, and assign 1 to a row where it matches and 0 to a row where it doesn't. Then set aggregation to Total on those fields

Store? --> if JF='Store' then 1 else 0
WH?    --> if JF='Warehouse' then 1 else 0
Corp?  --> if JF='Corporate' then 1 else 0

Div     JF          R#     Store?     WH?     Corp?
A        Store     123       1            0           0
A        Corp      234       0            0           1
C        Warehouse  345  0            1           0
etc.

Then in your list report, drop in Div for the 1st column, Store? for the 2nd, WH? for the 3rd, and Corp? for the 4th. Let Cognos sum the 2nd-4th columns, grouped on Division.

Hope this helps