0

I am trying to create a calculation in Cognos based on a number of Criteria. For example, I want to get a count of all of the records in my data set that have the following criteria:

Data_Level = "Aggregate"
Problem_Area = "Request"
IsResearch = "No"
Substate_ID = "Incomplete - Cancelled"

I want to then call this value: 'Aggregated Data - Non Research'

Thanks in advance for your feedback. I'm conducting a feasibility test of Cognos compared to other BI Tools so I am trying to replicate the same report across tools to gauge their usability. So far I am finding Cognos the most difficult to find resources on.

Regards, Jason

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
JasonR
  • 399
  • 4
  • 8
  • 26

2 Answers2

0

I would just create an if then else field which gives you a 1 if the conditions are met and 0 if they are not, then total that.

total(If ([Data_Level] = "Aggregate" and [Problem_Area] = "Request" and [IsResearch] = "No" and [Substate_ID] = "Incomplete - Cancelled") then (1) else (0))

Daniel Wagemann
  • 741
  • 4
  • 6
  • That exactly the type of thing I am trying to do but it doesn't like the Syntax. I really want to like Cognos but it seem so difficult to use compared to other tools. – JasonR Feb 25 '20 at 21:20
  • Telling us the expression that you are trying and the error could help. Breaking the problem into smaller chunks could also help you identify where the problem actually is happening. – C'est Moi Feb 25 '20 at 22:25
0

Try the function total and define the scope

For example, Create a data item "Aggregated Data - Non Research"

Expression:

    If ([Data_Level] = "Aggregate" and [Problem_Area] = "Request" 
and [IsResearch] = "No" and [
Substate_ID] = "Incomplete - Cancelled") 
then (1) else (0)

Then you can add another data item, using the total function, with an expression that defines the scope:

Total([Aggregated Data - Non Research] for Company, Year, Month)
VAI Jason
  • 534
  • 4
  • 14