Skip to main content
Announcements
NEW: Seamless Public Data Sharing with Qlik's New Anonymous Access Capability: TELL ME MORE!
cancel
Showing results for 
Search instead for 
Did you mean: 
mhapanka
Creator
Creator

Set analysis in stacked bar chart

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)

1 Solution

Accepted Solutions
petter
Partner - Champion III
Partner - Champion III

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.

View solution in original post

7 Replies
petter
Partner - Champion III
Partner - Champion III

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.
mhapanka
Creator
Creator
Author

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

marinadorcassio1
Partner - Creator
Partner - Creator

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

petter
Partner - Champion III
Partner - Champion III

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

mhapanka
Creator
Creator
Author

I want to exclude the ones where [SystemTrigger]or [Modifier] is blank


Shouldn't it be

Count(

  {<

    Modifier_Status_PCI_Suggestion={'Differ'}

    ,newcolumn={'Product1'}

    ,ModelCount={">=1"}

    ,FLAG={'0'}    //Flag 0 so that the rows where neither is blank is included?)

    ,PLATFORM-={''}

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

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

  >}

  Productline

)

marinadorcassio1
Partner - Creator
Partner - Creator

Yes, depends on how you define your flag of course, it was just to give you the idea

mhapanka
Creator
Creator
Author

Thanks Marina! that helps