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

Gap between "if" and set analysis

Hello everyone,

I have a graph with this expression :

=sum( if(Abonnement_paiement='EMV'  and (Abonnement_Offre_nom='1 An'   or Abonnement_Offre_nom='1 Mois'

or Abonnement_Offre_nom='1 Semaine'  or Abonnement_Offre_nom='1 Jour')  , Compteur_abonnement_OK))

I want to put a set analysis instead of it, here is the expression I've tried :

=sum({$<Abonnement_paiement= {'EMV'}, Abonnement_Offre_nom={'1 An', '1 Mois', '1 Semaine', '1 Jour'}>} [Compteur_abonnement_OK])

The problem is that I don't get exactly the same results.

I don't get why..

What's the difference between those 2 expressions ? Which one is correct ?

Thank you for your help.

8 Replies
hic
Former Employee
Former Employee

If your fields (Abonnement_paiement, Abonnement_Offre_nom and [Compteur_abonnement_OK]) are found in different tables in the data model, the two methods can return different results. The Set Analysis will most likely return the result you want.

HIC

Not applicable
Author

Hi Henric

Thanks for your answer.

Yes, one of the fields come from another table.

I will use the set analysis expression then.

But I still don't understand why the result is different ?

tresesco
MVP
MVP

Set analysis filters the data once for the entire chart you are using, whereas IF filters row-wise. In other words, for your dimension values IF enabled expression would filter based on dimension values, while expression with set analysis would filter for all irrespective of dimension values. Have you considered this fact for your comprehension?

hic
Former Employee
Former Employee

The main difference is that the Sum() is made over different ntuples. In Sum(<Field1>), the summation is made in the table where Field1 is found, but in Sum(<Field1> * <Field2>) the summation is made in a new ntuple - in the Cartesian product between Field1 and Field2 - that could have more records that the first table.

In your case, you have three fields inside your Sum(), QlikView will create the Cartesian product between these fields and calculate the sum there.

HIC

Not applicable
Author

I am not sure I get it.

What you explain here is how the set analysis works, right ?  It does all the products between tables and then calculate what I want ?

And so, what does a 'if' do ?

Thanks again Henric.

hic
Former Employee
Former Employee

For Sum( {$<Abonnement_paiement={'EMV'}>} [Compteur_abonnement_OK] ) the summation will be made over the rows in the table where [Compteur_abonnement_OK] is found.

For Sum( If( Abonnement_paiement='EMV', [Compteur_abonnement_OK] )) the summation will be made over the rows in the ntuple created by the Cartesian product between the rows of [Abonnement_paiement] and [Compteur_abonnement_OK], i.e. in a table that probably has more records than in the case above.

HIC

Not applicable
Author

Very helpful post.  Thank you Henric.

so when is it good to use If(Sum()) vs Set analysis?  I have a client that used no set analysis in their applications since 2008 to date because they did not know about Set Analysis.  I am in the process of rebuilding their applications and data model to use Set Analysis rather than if-sums. 

I tell me clients that it is faster thus more efficient than use of if-sum.  Is this the case?

hic
Former Employee
Former Employee

Usually Set Analysis is faster - and more importantly - it is faster for large data sets, i.e. when you need the performance.

HIC