COGNOiSe.com - The IBM Cognos Community

IBM Cognos 8 Platform => COGNOS 8 => Report Studio => Topic started by: philelmousse on 05 Jan 2012 07:48:16 AM

Title: return the element with the most occurence
Post by: philelmousse on 05 Jan 2012 07:48:16 AM
Hello,

I have a table of stats with a column "searchterm". I would like display the "searchterm" which is the most recorded.

I tried to use the count function or the topCount but without success
Title: Re: return the element with the most occurence
Post by: charon on 05 Jan 2012 08:04:27 AM
hi,

would the function "rank" be a solution, maybe with an extra filter (rank<2) to render just one entry? Or did i missunderstand the problem you explained?
cheerz :p
Title: Re: return the element with the most occurence
Post by: philelmousse on 05 Jan 2012 08:34:25 AM
my table has a column like that :

bla
blabla
bla
bla
outlook

I want something to retrieve "bla". So I have to use count distinct and something else i think
Title: Re: return the element with the most occurence
Post by: philelmousse on 09 Jan 2012 10:14:32 AM
up :/
Title: Re: return the element with the most occurence
Post by: blom0344 on 09 Jan 2012 12:01:56 PM
[column]
count(distinct [column])  --> rankitem

define calculated item as  rank([rankitem]) --> ranker

drag this item into detail filter section:

ranker < 2 

set application of filter to 'After auto aggregation'

However, ties will still result in 2 or more rows returned