5 Replies Latest reply: Aug 1, 2015 12:44 PM by Daniel Nasseh RSS

    Bar Diagram - Each bar another function

    Daniel Nasseh

      Hello,

       

      i want to create a bar diagram.

      This diagram should contain of multiple BARS. Each BAR representing a controlnumer.

      Behind each controlnumber should be a function.

       

      I can model it within the loading script, but when i click on a bar then i dont see the underliing filters in the selection overview.

       

      I can make it work for 1 Bar per diagramm like the following.

      I create a bar diagram and add a dynamic dimension for instance:

      =if(hair_color='blond','Controlnumber1')

       

      Now the problem is - i try to add other controlnumbers. The if / else does not work for that.

      e.g.:

      =if(hair_color='blond','Controlnumber1',

      if(hair_color='blond' AND eyes='blue' ,'Controlnumber2'))

       

      (base formula = count(distinct personids)

       

      The problem about this is, that controlnumber 2 gets not shown because it is a subset of controlnumber 1. QlikView seems to only allow to display the bars if they are not overlapping.

       

      But my controlnumbers DO overlap.

      So how can i do this?

       

      Please keep in mind, that i want to maintain the connection to other tables and that when i click on a bar, i still want to see the conditions / filters for this bar (which works for a single bar).

       

      Can anyone help me out with this?

        • Re: Bar Diagram - Each bar another function
          Stefan Wühl

          Maybe it's just enough to order your conditions subset first:

           

          =if(hair_color='blond' AND eyes='blue' ,'Controlnumber2',

               if(hair_color='blond','Controlnumber1')

          )

           

          Or do you want to the records to be counted in the subset as well as in the superset?

          Then you need to link the records to both sets in the script.

            • Re: Bar Diagram - Each bar another function
              Daniel Nasseh

              Hello,

               

              thanks a lot for the hint. Actually now after appliing your solution it still not works as i want it to work.

              And yes, i think i want the records to be counted in the subset and the superset. Actually i would really prefer

              if those BARS / SETS are completely independent from each other.

               

              After appliing your solution i get:

              5 blond blue

              10 blon.

              ((In reality i should get 15 blond))

               

              So basically - i think your second part should be my solution:

              "Then you need to link the records to both sets in the script."

               

              Unfortunately i do not know how to do this. Could you maybe give me a code snippet? Or explain a bit further - i am pretty much beginner level in QlikView.

              • Re: Bar Diagram - Each bar another function
                Stefan Wühl

                Basically, you create a table with a n:m relation between personids and control numbers, something like

                 

                ControlNumber:

                LOAD personids,

                          'ControlNumber1' as ControlNumber

                FROM YourTableWithPersonIDsHairColorAndEyes

                WHERE hair_color = 'blond';

                 

                CONCATENATE (ControlNumber)

                LOAD personids,

                         'ControlNumber2' as ControlNumber

                FROM YourTableWithPersonIDsHairColorAndEyes

                WHERE hair_color = 'blond' AND eyes='blue';

              • Re: Bar Diagram - Each bar another function
                Antonio Mancini

                Hi Daniel,

                maybe You can use ValueList() .

                See attachment.

                Regards,

                Antonio