Author Topic: How to count distinct items with filtered condition?  (Read 114 times)

Offline HenriP

  • Associate
  • **
  • Join Date: Jun 2021
  • Posts: 1
  • Forum Citizenship: +0/-0
How to count distinct items with filtered condition?
« on: 07 Jun 2021 03:00:42 am »
Hi

I have a problem creating a calculation in a cube dimension that should show how many items there are with a certain status.

I have used count( 1 within set "set") but this gives me the total number of items for every status. How could I filter the count so that it would only count the items with certain status?

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 11,329
  • Forum Citizenship: +665/-10
  • Cognos Software Muppet
Re: How to count distinct items with filtered condition?
« Reply #1 on: 07 Jun 2021 10:33:05 am »
Hi

I have a problem creating a calculation in a cube dimension that should show how many items there are with a certain status.

I have used count( 1 within set "set") but this gives me the total number of items for every status. How could I filter the count so that it would only count the items with certain status?

Hi,

I'm assuming Status is an attribute in the dimension?

You could try filtering the set using a filter() function before using this in your count()?

eg
count(1 within set filter([your set of members],[Your Status attribute] = 'Whatever Status you need'))

Cheers!

MF.
Meep!