Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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:
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:
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
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
Do you have a sample application that illustrates your issue with some test data?
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
)
Yes, depends on how you define your flag of course, it was just to give you the idea
Thanks Marina! that helps