Set analysis in stacked bar chart

[mhapanka]

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)

[petter · Accepted solution]

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]

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]

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]

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

[mhapanka]

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]

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

[mhapanka]

Thanks Marina! that helps