23 Replies Latest reply: Mar 2, 2017 10:00 AM by Sunny Talwar RSS

    Set Analysis Question -

    Lauren Mills

      I'm having issues with an expression -


      Current Expression =




      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.

        • Re: Set Analysis Question -
          Sunny Talwar

          Not sure I understand the requirement completely. Would you be able to share an example or some images?

          • Re: Set Analysis Question -
            Lauren Mills

            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.






            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 -
                Sunny Talwar

                May be this:

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

                  • Re: Set Analysis Question -
                    Lauren Mills

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

                    • Re: Set Analysis Question -
                      Lauren Mills

                      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 -
                        Lauren Mills

                        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 -
                            Lauren Mills

                            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 -
                                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)

                                  • Re: Set Analysis Question -
                                    Lauren Mills

                                    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 -
                                        Sunny Talwar

                                        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 -
                                            Lauren Mills

                                            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 -
                                                Sunny Talwar

                                                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 -
                                    Shahbaz Khan Mohammed

                                    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)



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