1

I have an IF statement (Please see below), it works but also gives me the true result and also the False result in some of the cases.

IF([Payroll History].[Deduction/Benefits Pay History].[Deduction/Benefit Type Code] ='MED' 
  AND [Payroll History].[Deduction/Benefits Pay History].[Deduction/Benefit Code] <> 'MDHRA') 
      THEN ([Payroll History].[Deduction/Benefits Pay History].[Deduction]) 
         ELSE ('N')

I just need the true result and I don't need to see the "N" for the highlighted rows.    enter image description here

You might get an idea why this happening, but I don't know how to remove raws with "N" when they have True result. Please see below.

enter image description here

Note - I am using Cognos 11 (BI UltiPro Reports)

dougp
  • 2,810
  • 1
  • 8
  • 31
bidevkelly
  • 23
  • 4

1 Answers1

0

Take a count for new deduction with a scope something like this

count(distinct [new deduction] for [company], [year], [employee number])

filter

([count deduction] = 1) OR ([count deduction] > 1 and [new deduction] <> 'N')
VAI Jason
  • 534
  • 4
  • 14
  • 1
    If I get it right, do I have to create a new column with the first one then add a filter on it. Sorry, it been a long time I have used Cognos, I can not remember anything. I would appreciate it if you could tell me how to implement it. Thank you so much – bidevkelly Jun 03 '21 at 23:22
  • Sure. You can create a new column with the count calculation to make testing a little easier. This way you can see the results. If it works the way you want AND you don't want the new column on the list, just cut (instead of delete) and it will stay in the query. Let me know what steps you need help with and I will give more info – VAI Jason Jun 04 '21 at 14:51