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
1) Use the count function.
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.
Hi,
1.
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]
CASE WHEN [Year] = 2016
THEN [Revenue]
ELSE 0
END
Data Item: [Revenue - Last Year]
CASE WHEN [Year] = 2015
THEN [Revenue]
ELSE 0
END
Then for your requirement:
Quote
if [row1] is equal to [row2], add the value in "amount column" minus 5
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
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