Subfield and Count in Expression

[sarathi_pm]

Hello,

I have a column like below which has the data in a pipe delimited form,

User	Subject
User 1	Maths|Chemistry|Physics
User 2	Chemistry|Maths
User 3	Physics
User 4	Chemistry|Physics
User 5	Physics|English

I want to get the Count of users for each of the subject.

Below is the output I am looking for and I am stuck up in getting the correct expressions in Dimension and in Expression.

Subject	User Count
Maths	2
Physics	4
Chemistry	3
English	1

I am able to use Subfield(Subject,'|') for the calculated dimension and I am able to get the unique list of subject in the dimension. But I am not able to get the expression to get the count of users.

Thanks

Sarathi

[tresesco]

In the UI or script?

PFA for a script solution.

SubField(Subject, '|') as SubjectSeparated

[sarathi_pm]

Hello Tresesco,

Thanks for the response. this is for Chart Dimension. I am able to get the output with the expression you have sent. But I am doing one more step on top of this.

When the user 1 logs in, I need to see the below output, When I add another expression for "Logged in User", that is not evaluating.

The output I am desiring is,

Subject	User Count	Logged in User
Maths	2	1
Physics	4	1
Chemistry	3	1
English	1

I am not using section access since I will not get the User Count from other users if use section access.

I using another table with NTName which is similar to the User.

Any help on how to achieve this?

Thanks

Sarathi