how to equate 2 fields in SET ANALYSIS?

[Anonymous]

I have an if function: count(distinct if( [Product Code] = [UPC Code],ProductID)). how do I count ProductID using set analysis.

Thanks,

[Oleg_Troyansky]

You have a few options, but before that - keep in mind that Product should not be one of your Chart Dimensions. Set Analysis can not be sensitive to chart dimensions, because the selection is calculated once, before the chart is calculated.

With that in mind, here are a couple of different options:

Select [Product Code] based on the selected values of [UPC Code]:

count( {<[Product Code] = [UPC Code]>}  distinct ProductID)

Select [Product Code] based on the possible values of [UPC Code]:

count( {<[Product Code] = P( [UPC Code] )>}  distinct ProductID)

The difference between the two is very subtle and often non-existent, so pick your preferred syntax.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[Anonymous]

Thanks Oleg,

My real scenario is a little different. I have some more conditions:- count( {< [Field A]={'Active'}, [Field B]= {'Red','White','Blue'}, [Field C]={'On Demand'}, [Product Code] = [UPC Code]>}  distinct ProductID)

the first 3 condition is about the field value but the last one, which is my main concern is about equating 2 fields

not field values. It was a little strange, I followed what u said, and although there was a red line, it brought me the calculations at first and now it is not returning any lines/values. I was thinking more like defining the fields to be equated

with a variable if set analysis is to be used.

the above expn works fine with IF condition but not as good performance wise. Can you suggest with example of a variable in set analysis format. and how about if the condition is not equal to instead of equal to??

Thanks.

[Oleg_Troyansky]

Your explanation is a bit confusing... I'm not sure that I understand your problem. If you could create a simple example and illustrate what's not working, I could possibly suggest a better solution.

[Anonymous]

hi,

Try:

Count( {< [Field A]={'Active'}, [Field B]= {'Red','White','Blue'}, [Field C]={'On Demand'}, [Product Code] = {'=[Product Code] = [UPC Code]'}>}  distinct ProductID)

[Gysbert_Wassenaar]

Comparing fields in a set analysis expression can lead to unexpected results. I think you need to take a look at this qlikview document: set_analysis_intra-record.qvw

[jose_vargas]

Hi. I tried something like you post, but Im no getting result. Y Try to do this.

if(Match([Razón Correción],'Discrepancia DAC') and (Categoria_Key=Categoria_Clasificada_Key or (Categoria_Key<>Categoria_Clasificada_Key and Categoria_Key <> Categoria_Detectada_Key)) ,  sum(Cantidad_Anomalia))

I want to do this with set analysis.but i dont have result.

sum({<[Razón Correción] = {'Discrepancia DAC'},[Categoria_Key]= {'=[Categoria_Key] =[Categoria_Clasificada_Key]'} >}[Cantidad_Anomalia])

Do you have any idea?

[alan_richmond]

Hi Oleg

I purchased a copy of your book, love the book,  but cant find the supplemental data files on the publishers site, are you able to send me a copy of the data files please?

Regards,

Alan