COGNOS Report: COUNT IF

Question

I am not sure how to go about creating a custom field to count instances given a condition.

---

I have a field, ID, that exists in two formats:

1. A#####
2. B#####

I would like to create two columns (one for A and one for B) and count instances by month. Something like COUNTIF ID STARTS WITH A for the first column resulting in something like below. Right now I can only create a table with the total count.

+-------+------+------+
| Month | ID A | ID B |
+-------+------+------+
| Jan   | 100  | 10   |
+-------+------+------+
| Feb   | 130  | 13   |
+-------+------+------+
| Mar   | 90   | 12   |
+-------+------+------+

Answer 1

Define ID A as...

CASE
  WHEN ID LIKE 'A%' THEN 1
  ELSE 0
END

...and set the Default aggregation property to Total. Do the same for ID B.

Answer 2

Try this

• Query 1 to count A , filtering by substring(ID,1,1) = 'A'
• Query 2 to count B , filtering by substring(ID,1,1) = 'B'
• Join Query 1 and Query 2 by Year/Month
• List by Month with Count A and Count B

Answer 3

Apologies if I misunderstood the requirement, but you maybe able to spin the list into crosstab using the section off the toolbar, your measure value would be count(ID).