Issue with count in expression of a straight table

[Anonymous]

Hi, I have column name data_access in my table. It contains two values Public and non-Public . In my chart I have to prepare two columns, One will contain the count of public and other will contain the count of non-public for a particular Employee_ID. Something like I have shown in second table. I am using a straight table chart and in expression I am using  :

"if (Data_Access='Public',count(Data_Access))" but I am getting 0 where there are counts for both Public and non-Public for a record. Please help!

Employee_ID	Data_Access
1	Public
1	Non-Public
1	Public
2	Public
2	Non-Public
3	Public
3	Non-Public
3	Non-Public
3	Public
3	Non-Public
4	Non-Public

Employee_ID	Public	Non-Public
1	2	1
2	1	1
3	2	3
4	0	1

[Anil_Babu_Samineni · Accepted solution]

Perhaps this?

count({<Data_Access = {'Public'}>} Data_Access)

count({<Data_Access = {'Non-Public'}>} Data_Access)

[Anonymous]

Thanks Anil. I have used below in expression and it's working fine now:

count(distinct if(Data_Access='Public', Data_Access) )

[Anil_Babu_Samineni]

In fact, I don't prefer to use that. Instead try this and efficient

count({<Data_Access = {'Public'}>} DISTINCT Data_Access)

Note - Please close this thread (Information available Qlik Community Tip: Marking Replies as Correct or Helpful)