If you are unable to create a new account, please email support@bspsoftware.com

 

News:

MetaManager - Administrative Tools for IBM Cognos
Pricing starting at $2,100
Download Now    Learn More

Main Menu

Counting Certain Entries in a Row

Started by Cognos_Jan2017, 20 Feb 2020 02:19:57 PM

Previous topic - Next topic

Cognos_Jan2017

Simplified List ...

UniqueID  Primary  Secondary  Tertiary  Count Where Same Entry 2 or 3 Times
    123          A             A             B                                2
    345          A             A             A                                3
    678          A             B             C                               
    901          C             A             C                                2

There will be a manageable number of different entries where a long Case When
should "Count" the number of entries looking for only 2 or 3.

Not sure a "Count" for the [UniqueID] by specific values in Primary, Secondary, and
Tertiary can be written?

The purpose of the above is to add a Filter to display records in a List where the
'Count Where Same Entry 2 or 3 Times' is >1.

Thoughts please.

Thank you, Bob

Cognos_Jan2017

S O L U T I O N

We solved this by adding a Field of ...

Case
When (([Primary]='Example') and ([Secondary]='Example') and ([Tertiary]='Example')) Then 3
When (([Primary]='Example') and ([Secondary]='Example')) Then 2
When (([Primary]='Example') and ([Tertiary]='Example')) Then 2
When (([Secondary]='Example') and ([Tertiary]='Example')) Then 2
End

The above Case When ran code for 16 different entries we needed to quantify.

Then with a 2 or 3 result, we added a Style Variable to show Green for 3, and Yellow for 2.

Hope this helps for a similar situation you may encounter, Bob