6 Replies Latest reply: May 22, 2017 11:42 AM by Robert Svebeck RSS

    Set1 Difference Set2

    Craig Hall

      Hi all,

       

      Am new to Qliksense, but a very experienced dev in C#, SQL and data analysis.

       

      Want to do the following.

       

      I am writing a Doctor-Peer Group comparison dashboard.

      I've got a column called "Consultant Name".

       

      I've got 2 filter panes, both linked to the same column of Doctors. {Consultant_name}

       

      I want to select consultant on the LEFT (Dave Smith) and then I want Dave Smith to be removed from the possibles on the RIGHT.


      The idea being that Dave Smith's average operating time is compared with his group peers.

      Also - I intend for there to be scope for comparing multiple Doctors in SET1, with {SET2/SET1}.

       

      So in set notation {CONSULTANT_NAME} / {SELECTED}.

       

      Can't seem to nail the syntax no matter what I do.

       

      Help would be appreciated. Once I see the correct syntax, it should all make sense from there.

      Craig.

        • Re: Set1 Difference Set2
          Robin Hausdörfer

          should work somehow like that:

           

          count({< FILTERFIELD1=E(FILTERFIELD2) >} "CONSULTANT_NAME")

          • Re: Set1 Difference Set2
            Robert Svebeck
            Have a look at this thread. It may help you:



            https://community.qlik.com/thread/237894


            Robert

            • Re: Set1 Difference Set2
              Craig Hall

              Yeah, I'm struggling a bit here.

               

              This is probably more of a design question. I need to control the selections in the filter pane, and then drive the calculations. This problem breaks everything that QlikSense is meant to do!

               

              So - from the top.

               

              Here's my dashboard - I want you to pick a code on the left (1. Pick a procedure code.)

              This gives you a set of possibles in 2. Select one or more Consultant in Step2.

              This then needs to remove the step 2 selection from the step 3. Filter peer group.


              Then Step 2 filter pane needs to drive Mean Op Time (Selected) and Step 3 filter pane needs to drive  Mean Op Time (Peers).


              I can do more advanced calculations, once I've figured out HOW to wire this stuff up


              Here's a screenshot.


              Thanks,

              Craig.


              Untitled.png


                • Re: Set1 Difference Set2
                  Robert Svebeck

                  Hi Craig

                   

                  Your question is very valid and there are many examples where we need to compare one scenario to other "possible" scenarios. The most comon example is when we want to compare a sales guy performace in a region towards the other sales guys in the same region.

                   

                  In Qlikview this is easy handled in what is called Alternate States - but there is not yet the same functions in Sense standard application.

                   

                  However we can create it in Sense by SET analysis and Data Islands.

                   

                   

                  To do what is needed, we need to create two new tables where the selections are made.

                  So in my script I have the Data table, and from that table I create two new tables:

                   

                  DataIsland1:

                  Load

                    System,

                      Consultant as ConsultantChoice1

                  resident Data;

                   

                  DataIsland2:

                  Load

                    System,

                      Consultant as ConsultantChoice2

                  resident Data;

                   

                  Then I drop those on the canvas like normal filter panes, and the first KPI indikator use the folowing expression:

                   

                  Sum({<Consultant=p(ConsultantChoice1)>} Value)

                   

                  This expression display the sum of value for the choosen consultant in the filter pane for Consultant1

                   

                  while the second KPI uses this formula:

                   

                  avg({<Consultant=p(ConsultantChoice2)>} Value)

                   

                  This expression display the average of values for the possible consultants available in Consultant2

                   

                  I made an example qvf that has your kind of setup. Have a look at it and see if it solves your problem.


                  Regards

                   

                  Robert