9 Replies Latest reply: Dec 5, 2012 8:58 AM by Mark Sheraton RSS

    Set Analysis or aggregrate problem??



      I have the following problem.


      Background information :


      We have a Mailman, everytime he goes for a delivery he gets a unique id (StopID), in 1 delivery(StopID) he can deliver mail to 1 or more clients.


      Example :


      StopID          Client          #Packages

      1                   A               1

      1                   C               1

      2                   A               9

      2                   B               8

      3                   C               1

      4                   A               5

      4                   D               3

      4                   C               1

      5                   D               1

      5                   A               1

      5                   B               5


      What do i want to see ? Whenever i put the client name in my Qlikview report (made a input box with variable vSynDeb) i want to see all the client names the mailman also delivered in the same StopID.


      Example : if i input Client C into the inputbox i want to see :

      Client : A,C,D


      if i input Client A i want to see:

      Client : A,B,C,D


      How do i do this ?


      i made a straight table with the following expression:


      = if(sum (




      if(count({$<Client>} StopID) > 1, 1,0)


      if(count({$< Client = {'$(vSynDeb)'} >} DISTINCT StopID) > 1, 2,0)






      This way i managed to get all StopID numbers where a mailman delivered to the selected (vSynDeb) client and to other clients on the same StopID. And now im stuck. How do i get the other Client names in the table ?? whnever i insert Client dimension to chart the chart only show 1 Client name and not all the client names that the mailman delivered to on that specific StopID

        • Re: Set Analysis or aggregrate problem??
          Celambarasan Adhimulam

          Try with this

          StopID and ClientName as Dimension


          If(Count({<StopID=P({<Client={'$(vSynDeb)'}>} StopID)>})>0,1,0)


          Hope it helps

            • Re: Set Analysis or aggregrate problem??



              The expression is not working for me. no results are shown.


              What does StopID=P mean ?


              It does show names now, but too much names. i expect +/-16 unique Client names but got much more +10000ish
              I also got StopID numbers with other client names ? I selected (example)Client A  and now i  also see StopID where only Client D is in it.

            • Re: Set Analysis or aggregrate problem??
              Mark Sheraton

              Create a listbox for Client and select A


              Now create a chart with Client as the dimension and the following expression:

              Count({1<StopID = p({1<Client={$(=Only(Client) )}>}) >} Client)



              What does StopID=P mean ?



              See the heading "Set Modifiers with Implicit Field Value Definitions"

              on page 809 of the Qlikview reference manual.


              Return the clients exludng the current selection (ie you select A but this still returns A,B,C & D)

              but only those clients who have have StopID the same as the selected client (A).


              (see the attached example)


              Update -

              (Didn't read down this thread before piosting and see Celambarasan Adhimulam had already replied, might be useful so will leave this response.)

                • Re: Set Analysis or aggregrate problem??



                  The expression you showed is working but with 1 issue. The table does never change whatever you select(other than Clientname), it now shows every clientname where the StopID is the same as the selected.


                  I also want that it changes when i select my date field. how do i implement this to the expression of yours ? Sorry that i didn't mention the date field in my first post.




                  With your expression i get 42 unique Client names ( this is all the names ever in history where the StopID of the selected Client was the same as another Clients StopID) This number does not change when i select December, in December i must only see the Client names where the StopID in december of the selected client is the same as other clients,  in this case 16 unique names.