7 Replies Latest reply: Aug 23, 2018 10:07 AM by Mallika Mhapankar RSS

    Set analysis in stacked bar chart

    Mallika Mhapankar

      Hi Guys,

      I'm stuck with the wrong output due to some mistake in my set analyses.

       

       

      I want to make a stack bar chart showing : match, mismatch and investigation required

      To get the count of  match, mismatch and investigation reqd across the field: productlines, I have to perform set analyses.

      The requirements are as follows:

      For mismatch:

      only the entries under field 'status' as 'Modifiers Differ' and field 'newcolumn' should have 'product1' and ([Volume]>=1) and (([ProductLine]="29") Or ([ProductLine]="89") Or ([ProductLine]="9F") Or ([ProductLine]="AJ") Or ([ProductLine]="GE") Or ([ProductLine]="MA") Or ([ProductLine]="PT") Or ([ProductLine]="RB") Or ([ProductLine]="UF") Or ([ProductLine]="VI") Or ([ProductLine]="XF"))
      and ([SystemTrigger]!="" Or [Modifier]!="")


      where and is intersection and or is union


       

      My code below is giving me same values for match, mismatch and investigation required

      Could someone please correct this ?



      =Count({<[Modifier_Status_PCI_Suggestion] = {" Differ"}>

      *<[newcolumn] ={'Product1'}>

      *<ModelCount={">=1"}>

      *(<[SystemTrigger] -= ""> + <MODIFIER -= "">+ <PLATFORM -= ""> + <[Modifier Candidature] = {"Modifier Candidate"}>)

      + <PRODUCTLINE={"29", "89", "9F", "AJ", "GE", "MA", "PT", "RB", "UF", "VI", "XF"}>}Productline)

        • Re: Set analysis in stacked bar chart
          Petter Skjolden

          You are using set operations between record/row sets which is not necessary in this situation. It only complicates the set expression. The and and or operations comes naturally out of a single record set by doing this instead:

           

          =Count(

            {<

               Modifier_Status_PCI_Suggestion={'Differ'}

              ,newcolumn={'Product1'}

              ,ModelCount={">=1"}

              ,SystemTrigger-={''}     // Exclude empty strings

              ,MODIFIER-={''}

              ,PLATFORM-={''}

              ,[Modifier Candidature]={'Modifier Candidate'}

              ,Productline={'29', '89', '9F', 'AJ', 'GE', 'MA', 'PT', 'RB', 'UF', 'VI', 'XF'}

            >}

            Productline

          )

           

          Remember:


          1. The names of fields are case-sensitive, Productline is NOT the same as PRODUCTLINE. Qlik regard them as two different fields if both of them exists. Be sure that all your fields are used with the same caseing as in your data model.
          2. The quotation marks ' and " mean different things - sometimes they can give the same result. The single quotation marks are for matching literal strings which could also be formatted dates and numbers too actually. 0 and positive numbers don't need single quotation marks. However negative numbers do need them for some strange undocumented reason. The double quotation marks are for searches.
          3. The comma between the fieldnames mean "AND"
          4. If you have a list of values the list is like an "OR" just like you have in Productline in the expression I show above.
          5. You always need to enclose a values or search strings in curly braces/brackets because they are element sets even if the set only contains a single value or no value (the empty set).
          6. The version of QlikView and Qlik Sense matters when it comes to quoting as the more recent versions follow more strict rules when it comes to handling them differently and correctly.
            • Re: Set analysis in stacked bar chart
              Mallika Mhapankar

              Hey Peter, Thanks so much for the help and the lessons.

              After using the set analysis given above (taking care to name the fields correctly), I am getting no bars- the values are zero.

               

              What could possibly be causing this since the set analysis function you gave looks perfect to me

                • Re: Set analysis in stacked bar chart
                  Marina Dor

                  Hi Mallika,

                   

                  Stupid thing, but check the case, this is really important

                   

                  and maybe...

                   

                  =Count(

                    {<

                      Modifier_Status_PCI_Suggestion={'Differ'}

                      ,newcolumn={'Product1'}

                      ,ModelCount={">=1"}

                      ,MODIFIER-={''}

                      ,PLATFORM-={''}

                      ,[Modifier Candidature]={'Modifier Candidate'}

                      ,Productline={'29', '89', '9F', 'AJ', 'GE', 'MA', 'PT', 'RB', 'UF', 'VI', 'XF'}

                    >}

                    Productline

                  )

                  +

                  Count(

                    {<

                      Modifier_Status_PCI_Suggestion={'Differ'}

                      ,newcolumn={'Product1'}

                      ,ModelCount={">=1"}

                      ,SystemTrigger-={''}     // Exclude empty strings

                      ,PLATFORM-={''}

                      ,[Modifier Candidature]={'Modifier Candidate'}

                      ,Productline={'29', '89', '9F', 'AJ', 'GE', 'MA', 'PT', 'RB', 'UF', 'VI', 'XF'}

                    >}

                    Productline

                  )



                  OR

                  Create a new field

                  if(([SystemTrigger]!="" or [Modifier]!=""), 1, 0) as FLAG

                  in your script, and then :

                  =

                  Count(

                    {<

                      Modifier_Status_PCI_Suggestion={'Differ'}

                      ,newcolumn={'Product1'}

                      ,ModelCount={">=1"}

                      ,FLAG={'1'}    //equals your last condition ([SystemTrigger]!="" Or [Modifier]!="")

                      ,PLATFORM-={''}

                      ,[Modifier Candidature]={'Modifier Candidate'}

                      ,Productline={'29', '89', '9F', 'AJ', 'GE', 'MA', 'PT', 'RB', 'UF', 'VI', 'XF'}

                    >}

                    Productline

                  )

                   

                   

                  Regards,

                  Marina

                  • Re: Set analysis in stacked bar chart
                    Petter Skjolden

                    Do you have a sample application that illustrates your issue with some test data?