How to solve this with SET ANALYSIS

[Anonymous]

I m trying to use set analysis to count with the following and it is not working, the IF statement is working though.

count({<[Flag Field]-={'1'},

[Status]={'ACTIVE'},[Field A]={'Blue'},[Field B]-={'Plain','Color'}>}

Distinct [Product ID])

what is wrong in the above Expn

count(distinct if([Flag Field]<> 1 and [Status]='ACTIVE'

and [Field A]='Blue' and [Field B]<>'Plain' and [Field B]<>'Color'

,[Product ID]))

Flag field is a calculated field in the script as below:

if(isnull(FieldA) and Main_start_DT < Main_End_Dt

  ,1) as "Flag Field"

[sunny_talwar]

Try this may be:

=Count({<[Flag Field] = e({<[Flag Field] = {1}>}), [Status] = {'ACTIVE'}, [Field A] = {'Blue'}, [Field B] = e({<[Field B] = {'Plain','Color'}>})>} Distinct [Product ID])

[Anonymous]

It is not working Sunny. do you have any other suggestion

[marcus_sommer · Accepted solution]

I suggest to extend the flag-field to a real else-part like:

if(isnull(FieldA) and Main_start_DT < Main_End_Dt, 1, 0) as "Flag Field"

and then to query them within your set analysis:

count({<[Flag Field] = {0}, [Status]={'ACTIVE'},[Field A]={'Blue'},[Field B]-={'Plain','Color'}>}

     Distinct [Product ID])

- Marcus

[evan_kurowski]

Hello QlikDash,

As Marcus's solution points out, your current [Flag Field] has two possibilities: 1, Null().

The NOT set-analysis operand will not be able to count up the Null() rows of a field.

The following test cannot "grip" the Null area of [Flag Field]:

=Count({<[Flag Field]-={1} >} [Flag Field])

The following can help you measure the existence of Null areas (but not using set-analysis):
=NullCount( [Flag Field])

So per his suggestion, give the areas of missing information a handle (assigning them '0' instead of Null), should give no problem isolating EQUAL and NOT EQUAL areas in set-analysis.

[Anonymous]

Marcus and Evan Thank you for the suggestion.

I made the change to include 0 in the script :  if(isnull(FieldA) and Main_start_DT < Main_End_Dt, 1, 0) as "Flag Field"

and assign the Flag field to {0} in the set analysis but it is not working

[evan_kurowski]

Before you evaluate the user-interface expression, are you certain the script flag is working?

When you check the flag set to 1|0, is it doing so properly according to your targeted logic?  The IsNull() function won't pick up empty string or space, because they aren't truly null, and I find that If(Len(Trim(FieldA)) is often a more flexible predictor of missing or empty info.

Also, check your Main_start_DT <  Main_End_Dt evaluation, and ensure that is evaluating properly.

Put the [Flag Field] in a list box by itself and check the 'Show Frequency' options.  Does the frequency distribution cover areas of both 1 & 0, or is it 100% on just one value.  If it 100% on just one value, the script evaluation probably has the issue.

[Digvijay_Singh]

While going through your expression I found something which I was not aware of - the case-in-sensitivity of set analysis, I used your expression and started new discussion to understand it better - Re: Set Analysis - is it case insensitive ?

Your expression worked fine with the sample data I used so I think you may need to see your data further to understand why it is not working -

Just attaching the sample I have used, may be it can be of any help.

Thanks,

DJ

[marcus_sommer]

I believe like Digvijay that there are further reasons why it's not worked for you at the moment and think you should look at his answer.

- Marcus

[evan_kurowski]

I m trying to use set analysis to count with the following and it is not working, the IF statement is working though.

count({<[Flag Field]-={'1'}, 

[Status]={'ACTIVE'},[Field A]={'Blue'},[Field B]-={'Plain','Color'}>}

Distinct [Product ID])

what is wrong in the above Expn

count(distinct if([Flag Field]<> 1 and [Status]='ACTIVE'

and [Field A]='Blue' and [Field B]<>'Plain' and [Field B]<>'Color'

,[Product ID]))

Flag field is a calculated field in the script as below:

if(isnull(FieldA) and Main_start_DT < Main_End_Dt

  ,1) as "Flag Field"

While I agree bugs related to inconsistency of set-analysis/search case-sensitivity is a major concern (please fix this asap, because I ❤️ set-analysis), I'm not certain it is the only possibility here. 

You can see in the requested set-modifiers, there is variance in attempts to match by case.  The [Status] modifier sought is upper, while [Field A], [Field B] requests are capitalized.

[Status]={'ACTIVE'},  //attempt to match a fully uppercase value
[Field A]={'Blue'},  //attemp to match capitalized value
[Field B]-={'Plain','Color'} //attempt to match capitalized value

This implies two possibilities:

1. The variance in case in set-modifiers was inadvertent and QlikDash was not paying attention to case when forming set-analysis. (A big Qlik no-no, as Qlik developers are made aware at nearly all stages that case sensitivity matters)
   
2. The variance in case of requested set-modifiers was deliberate and QlikDash was specifically trying to match case-sensitive values.
   

In which case, the set-analysis case-sensitivity bug would only come into play if:

- the actual contents of a set-modifier are a case insensitive match within the search field
- are mutually exclusive in terms of a case-sensitive match.

(i.e. the expression would only be bug impacted under the following conditions:

[Status] Set-modifier = ‘ACTIVE’   | field only contains:  ‘active’,’Active’

[Field A] Set-modifier = ‘Blue’   |   field only contains:  ‘BLUE’,’blue’

[Field B] Set-modifier = ‘Plain’,’Color’  |   field only contains:  ‘PLAIN’,’plain’,’COLOR’,’color’

I'm not listing every variant of case possibility like plAiN, coLOr, etc.. but you get the idea)



However…

QlikDash goes on to assert that the following IF statement is working:
=count(distinct if([Flag Field]<> 1 and [Status]='ACTIVE'
and [Field A]='Blue' and [Field B]<>'Plain' and [Field B]<>'Color'
,[Product ID]))

As far as we are aware, IF statements do not have an issue matching on case-sensitivity.    Which implies the following field values are present and being properly detected in the data:

[Status]: ‘ACTIVE’

[Field A]: ‘Blue’

[Field B]: ‘Plain’, ‘Color’

Based on the working IF statement providing “proof of field contents”, if these field values are indeed present, then the set-modifiers from the set-analysis version should still be able to produce correct results, regardless of the bug  (non-wildcard equi/NOT equi matches are not broken when their exact case-sensitive matches are present in the data).