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

    Set Analysis or aggregrate problem??

      Hi,

       

      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 (

       

      Aggr(

       

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

      +

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

       

      ,StopID)

      )

      =3,1,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

          Expression:

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

           

          Hope it helps

            • Re: Set Analysis or aggregrate problem??

              Hi,

               

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

                  Hi,

                   

                  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.

                   

                  UPDATE:

                   

                  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.