COGNOiSe.com - The IBM Cognos Community

Hi,
I created a query item as below
Count (distinct case when item =1 then Id else null end)
Like this I have more 4 condition with same Id count
Count (distinct case when item =2 then Id else null end)
Count (distinct case when item =3 then Id else null end)
Count (distinct case when item =4 then Id else null end)
Count (distinct case when item =5 then Id else null end)
In spite of creating separate query item,  can we create single query item.

Thanks
Inam\

Quote from: inu on 06 Oct 2017 11:56:46 PM
Hi,
I created a query item as below
Count (distinct case when item =1 then Id else null end)
Like this I have more 4 condition with same Id count
Count (distinct case when item =2 then Id else null end)
Count (distinct case when item =3 then Id else null end)
Count (distinct case when item =4 then Id else null end)
Count (distinct case when item =5 then Id else null end)

Hmmm.
I think they way you have defined it is correct.
It would appear to want separate DISTINCTly counted measures
Having these in a single CASE statement for example I don't think would work

ex.
CASE
   WHEN [Gender] = 'M' then [Employee_ID]  ELSE null
   WHEN [Gender] = 'F' then [Employee_ID]  ELSE null
   ELSE null
END

This above would not give you separate counts for Male and Female.
Like your example - you would need to have separate expressions

Name:  Male Count
IF [Gender] = 'M' then [Employee_ID]  ELSE null

Name:  Female Count
IF [Gender] = 'F' then [Employee_ID]  ELSE null
   
Good luck