-
Re: Set Analysis Question -
Not sure I understand the requirement completely. Would you be able to share an example or some images?
-
Re: Set Analysis Question -
Lets say I have 4 Classes I have to take in school...
Course 1
Course 2
Course 3
Course 4
And they courses get bundled up into a grouping or grade level. That grouping or level is identified by an id called Rule Group Rule = 10682.
Instead of getting a grade in the classes you get a status (transcript_status_code={'N','L','S'})
N - No You Haven't Passed the Class
L - You've Launched the Online Class
S - You've Scheduled the Class
^ all 3 statuses mean that you haven't completed the classed yet.
I want to count the number of Employees (employee_id)
Who has not completed (transcript_status_code={'N','L','S'}) ALL 4 courses (course_name)
By level (rule group id = 10682)
LEVEL 1 (identifier as 'Rule Group Id')
Course 1 (All Courses Identifier as 'Course Name')
Course 2
Course 3
Course 4
N - No You Haven't Passed the Class (All Status Identifiers as 'transcript_status_code={'N','L','S'})
L - You've Launched the Online Class
S - You've Scheduled the Class
And a Count on Employee (employee_id)
Does this help?
-
Re: Set Analysis Question -
May be this:
Count({$<employee_id = {"=Count(DISTINCT {<rule_group_id={10682}, transcript_status_code={'N','L','S'}>} course_name) = 4"}>} employee_id)
This will count only those employee_id where count of all four course_name for a particular employee_id is either N or L or S
-
-
Re: Set Analysis Question -
The count I got on the left is by course - (10 current courses - but theses courses could come and go all the time). I would assume that the left chart and the gauge should match by counts.
-
Re: Set Analysis Question -
May be this:
Count({$<employee_id = {"=Count(DISTINCT {<rule_group_id={10682}, transcript_status_code={'N','L','S'}>} course_name) = Count(TOTAL DISTINCT {<rule_group_id={10682}>} course_name)"}>} employee_id)
-
-
-
Re: Set Analysis Question -
Let me ask this - is there a way to reference a Master Item in an expression for a measure?
-
Re: Set Analysis Question -
I don't have experience with Qlik Sense, someone who has used Qlik Sense might be able to answer this better. Sorry
-
Re: Set Analysis Question -
You can use the whole Master Item as an expression. You cannot use it as a part of an expression.
-
-
-
-
-
-
-
-
-
-
Re: Set Analysis Question -
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!!
-
Re: Set Analysis Question -
May be this:
Count({$<transcript_status_code ={"N","L","S"}, course_id = p({<level = {'Level 0'}>})>}distinct employee_id)
-
-
Re: Set Analysis Question -
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.
-
Re: Set Analysis Question -
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...
-
Re: Set Analysis Question -
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
-
Re: Set Analysis Question -
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)
-
Re: Set Analysis Question -
Count({$<FirstSortedValue(transcript_status_code ={"A","X"}, last_update_date), category = {'Skill Area 1'}>} distinct employee_id)
^The other day you helped me with the first sorted value. Am I able to nest it within an expression like above?
-
Re: Set Analysis Question -
You will need to use Aggr() inbetween Count and FirstSortedValue().... but I don't know what you are trying to do here
-
Re: Set Analysis Question -
I'm trying to only do a count based on that last_update_date transcript_status. When I do my count... It's counting all transcript_status and not the most recent. That's why I want to incorporate this piece into the expression.
Count({$<Aggr(FirstSortedValue(transcript_status_code ={"N","L","S","P","T"},-last_update_date), course_name = p({category = {'Skill Area 1'}>})>} distinct employee_id))
^Currently not working.
-
Re: Set Analysis Question -
May be like this
Count({$<transcript_status_code ={"N","L","S","P","T"}, course_name = p({category = {'Skill Area 1'}>})>} Aggr(FirstSortedValue(employee_id, -last_update_date), <Dimensions>))
But again, I don't think I understand your requirement well enough to tell you if the above is going to work. Can you mock up 10 rows of data (all dummy data) and explain what is that you need from it?
-
-
-
-
-
-
-
-
Re: Set Analysis Question -
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.
-
