Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

how to equate 2 fields in SET ANALYSIS?

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

Thanks,

7 Replies
Oleg_Troyansky
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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
Partner Ambassador/MVP
Partner Ambassador/MVP

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
Not applicable
Author

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


talk is cheap, supply exceeds demand
jose_vargas
Creator
Creator

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
Creator
Creator

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