Row Counts in Cognos + Row comparison

[a1003520]

Hi All, assuming I have a sample table e.g

StaffNo   City            Income     Branch    Key                 KeyCount
A001      Utrecht       5000         BBB        UtrechtBBB         2
B001      Berlin          1000         XXX        BerlinXXX            3
F009       Utrech         9000        BBB        UtrechtBBB         2
M009      Berlin          9800         XXX        BerlinXXX            3       
V009      Berlin          2200         XXX         BerlinXXX            3

1) What formula can I use to achieve the KeyCount values (Its counting the number of multiple values in the key column)
2) Still base on the above table, If I want to remove any multiple value and only show distinct values e.g.
Branch    Key                 KeyCount
BBB        UtrechtBBB         2
XXX        BerlinXXX            3

3) How do I compare a previous row and a current row together e.g
Code  Amount
AA          17
AA          12  (17-5)
BB          100
BB          95   (100-5) 

if [row1] is equal to [row2], add the value in "amount column" minus 5

Thanks in advance

[dougp]

1)  Use the count function. 

Code Select Expand


count(Key for City)

To manage aggregation, you may need to create a new query fed by your current query.
2)  Cut the Income and StaffNo columns from the list.
3)  Good luck.  I wish Cognos did this.  I've had to create multiple queries of the same thing with row numbers offset by one and join them to do this.  It can make your report run really slow.  I'm hoping someone else here has a better idea.

[AnalyticsWithJay]

Hi,

1.

Code Select Expand

count([Key] for [StaffNo])

2. Auto Group & Summarize is enabled by default, which will summarize your data. A distinct is based on the row. If you only include Branch, Key, KeyCount in your data container, you should automatically get the output you provided. If you include other columns like StaffNo, you will get more than one row for BBB, since those rows are technically distinct.

3. I don't know your requirement, but this is a very common request, and you have to use a case statement. This is quite common in This Year vs. Last Year type metrics. Do not use multiple queries if you don't have to!

Here's an example on how to do it within the same query:

Data Item: [Revenue - Current Year]

Code Select Expand


CASE WHEN [Year] = 2016
THEN [Revenue]
ELSE 0
END


Data Item: [Revenue - Last Year]

Code Select Expand


CASE WHEN [Year] = 2015
THEN [Revenue]
ELSE 0
END



Then for your requirement:

if [row1] is equal to [row2], add the value in "amount column" minus 5

Code Select Expand


CASE WHEN [Revenue - Current Year] = [Revenue - Last Year]
THEN ...
ELSE ...
END


In your DB, these would be separate rows as such:
Key   |   Date         |   Revenue
123     2016-01-01         9854
456     2015-01-01         5465

[a1003520]

Thanks for the interesting solutions. Furthermore, could you explain how the count([column1] for [column2]) really work with an example.
Also can sum([column1] for [column2]) also work? And with example if possible