Set Analysis Question -

[#1]

I'm having issues with an expression -

Current Expression =

Sum({$<rule_group_id={10682},transcript_status_code={'N','L','S'}>}employee_id)

I am trying to count the number of employees (employee_id) who have the following transcripts (transcript_status_code={'N','L','S'}) for the rule group id (rule_group_id={10682}).

The part I know I am missing is that the rule group id points to multiple classes (course_name), however - I don't want to have to hand code in every course id.

I was able to make a master item (for a different visualization) ...

=if(rule_group_id = ('10682'),course_name)

^ Saying give me every course name that falls into this Rule Group ID. That works fine.

But I want to use the GUAGE visual and reference a % of students who have the transcript_status_code={'N','L','S'} for the rule group id and the courses that it points to.

It's probably simple but I know I'm missing one part of my puzzle with the course detail.

Any help is appreciated.

[sunny_talwar]

I don't have experience with Qlik Sense, someone who has used Qlik Sense might be able to answer this better. Sorry

[Anonymous]

You can use the whole Master Item as an expression.  You cannot use it as a part of an expression.

[#4]

Let's try this again....

I want to create a measure for a Gauge Visual.

I have the logic already in a table but need to get the Dimension part incorporated into the Measure I am trying to create.

Example:

On the Left - "Level 0 Courses" if being populated by a Master Dimension.

  =If(level='Level 0',course_id)

On the Right - I have a measure ...

  Count({$<transcript_status_code ={"N","L","S"}>}distinct employee_id)

I want to combine them so I can create a measure. It should give me a distinct count of employee id who has a transcript status code of (N,L,S) for any course id that is in Level 0. Then I will divide that by a distinct count of employee to get a %.

Please help me write this measure!!

[sunny_talwar]

May be this:

Count({$<transcript_status_code ={"N","L","S"}, course_id = p({<level = {'Level 0'}>})>}distinct employee_id)

[MK9885]

For me this looks very complicated but I just understood your last reply.

I'll create a dimension in script by preceding load (if those fields are coming from same table)

Ex: If(level='Level 0',course_id) as [Level 0 Courses]

And use this [Level 0 Courses] dimension in set analysis.

Count({$<transcript_status_code ={"N","L","S"},[Level 0 Courses]>}distinct employee_id)

Plus for your gauge

Count({$<transcript_status_code ={"N","L","S"},[Level 0 Courses]>}distinct employee_id)/ Count(Distinct(employee)

If it doesn't give me result cus of complex set expression, I'll store that Set expression as a Variable

vExpression = Count({$<transcript_status_code ={"N","L","S"},[Level 0 Courses]>}distinct employee_id)

and

$(vExpression)/Count(Distinct(employee)

Not sure if this will work, if Sunny is finding it hard then it's not possible for me, lol.

[#7]

I am going to try this shortly. I appreciate your feedback!

[#8]

Okay - I had a chance to try this but it doesn't work - Here is why.

I use Level 1 as an example ...

I want to get a % - of employees who have complete ALL course_id that are identified by level (Level 1) with a transcript_status_code of "A" or "X".

Count({$<transcript_status_code ={"A","X"}, course_id = p({<level = {'Level 1'}>})>}distinct employee_id)/count(distinct employee_id)

The Visual = ^ the above expression is in this visual and it is showing 100%. But based on the other information below - it should only show 0%.

The reason it should only show 0%...

There are two course_id (13860 & 14761). And the employee has a transcript_status_code of "S" and "X"

Based on the expression - I am only wanting the students who have completed ALL course_id with the level (Level 1).

That's why I was wondering how to right this to point to ALL course_id within the LEVEL I'm pointing at.

I WOULD LOVE LOVE LOVE to get some help on this one. My head hurts from thinking about it.

[#9]

Wait - I think this does work Sunny.

How would I add just one more thing to this?

category = "Skill Area 1"

But I would only want this part to stay the same regardless of filter. I want the number to change based on everything else though -

I'm confusing myself...

[#10]

Count({$<transcript_status_code ={"A","X"},{1<category = 'Skill Area 1'>},course_id = p({<level = {'Level 1'}>})>}distinct employee_id)/count(distinct employee_id)

^ It would be like a set analysis with a set analysis

[sunny_talwar]

May be like this

Count({$<transcript_status_code ={"A","X"}, category = {'Skill Area 1'}, course_id = p({<level = {'Level 1'}>})>} distinct employee_id)/count(distinct employee_id)