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"

[Anonymous]

Hi all,

Thank you so much for providing valuable feed back. The discussion took a different direction, however, probably for a much better insight and discovery surrounding set analysis. The goal behind my question was to find why set analysis was not "gripping" non-1 values. It was not really about case sensitivity. but my good luck, that issue was also identified and discussed with a result.

As it turns out, I was not able to count [Flag Field] with non-1 values because in my data model, I had a where clause which was loading records where [Flag Field] =1; i disabled that and set analysis correctly picked/"GRIPPED" as Evan_Kurowski‌ likes to call it.

one thing is clear though: If statement picks up non-1 values even when Flag Field is not defined with (1,0) whereas Set analysis seems to only work when Flag Field is articulately defined with a (1,0).

so I will pick Marcus suggestion because he pointed out first, the possibility of not gripping due to absence of (1,0) in the calculated field.

[Anonymous]

I have attached the qvw that has where clause restricting "0" values of Flag Field.

Thank you sunindia‌ MichelSommer‌ Digvijay‌ Evan_Kurowski‌

Please feel free to correct my understanding. I am dependent on qlik community for what I do to a good extent.

Thanks again