Author Topic: distinct count function in cognos  (Read 62339 times)

Offline bharath_hsbc

  • Associate
  • **
  • Join Date: Jul 2007
  • Posts: 4
  • Forum Citizenship: +0/-0
distinct count function in cognos
« on: 16 Jul 2007 11:58:38 am »
hi, i wish to know the distinct count function in cognos. I use count([subjects]) to count number of subjects. But if I want to count distinct subjects, what is the function i use ? Any help is appreciated. Thanks in Advance.

Regards,
Bharath

Offline MDXpressor

  • Global Moderator
  • Community Leader
  • *****
  • Join Date: Jan 2007
  • Posts: 219
  • Forum Citizenship: +16/-2
Re: distinct count function in cognos
« Reply #1 on: 20 Jul 2007 06:16:24 pm »
distinct count can be applied in 2 ways.

You can set the aggregation property of the data item to Count Distinct.  It should be understood thatd that you would replace the data item with the count using this method.  For instance, if you had a data item called Sales Rep Name and it had 22 distinct occurrences the data delivered would be 22, and not the Sales Rep Name you may be expecting.

Second is to script a data item:
Count(distinct [Data Item] for [Summary Key])
No, a proof is a proof. What kind of a proof? It's a proof. A proof is a proof, and when you have a good proof, it's because it's proven.

-Jean Chretien

Offline ms_anna

  • Associate
  • **
  • Join Date: Mar 2009
  • Posts: 3
  • Forum Citizenship: +0/-0
Re: distinct count function in cognos
« Reply #2 on: 01 May 2009 08:14:11 pm »
MDXPressor,

I have a similar question but it is regarding the accomodation of about 800K categories in a unique calculated column (as a dimension), knowing the limitations of max categories, is there a way i can have all these 800K categories counted through category count measure in transformer. Please give some detailed steps.


distinct count can be applied in 2 ways.

You can set the aggregation property of the data item to Count Distinct.  It should be understood thatd that you would replace the data item with the count using this method.  For instance, if you had a data item called Sales Rep Name and it had 22 distinct occurrences the data delivered would be 22, and not the Sales Rep Name you may be expecting.

Second is to script a data item:
Count(distinct [Data Item] for [Summary Key])

Offline arunavagupta

  • Associate
  • **
  • Join Date: May 2012
  • Posts: 1
  • Forum Citizenship: +0/-1
Re: distinct count function in cognos
« Reply #3 on: 17 May 2012 08:55:19 am »
hi,

i have a field called Status that has two values 'FAILURE' and 'SUCCESS'
now that i want to count the number of records with FAILURE
how to go about it?
have only one field in the package called Status
Actually need to calculate FAILURE %
that is number of failure/total status *100%
Can any one help me wth ths?

Offline TheCognosDave

  • Full Member
  • ***
  • Join Date: Sep 2013
  • Posts: 33
  • Forum Citizenship: +2/-0
Re: distinct count function in cognos
« Reply #4 on: 22 Oct 2013 09:06:07 am »
Just do it in a SQL statement..

SELECT Status, Count(*)
FROM <YourTableNameHere>
GROUP BY Status

This will return a rowset listing all the possible values of 'Status' (i.e. 'FAILURE' and 'SUCCESS' ), with their respective occurrence count.

Failure % would then be something like this..

([Failure Count] / ([Failure Count]+[Success Count]))*100

GL  ;)



 


       
Twittear