Qlik Community

QlikView Layout & Visualizations

Discussion Board for collaboration on QlikView Layout & Visualizations.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted

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

Highlighted
Not applicable

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 ?

Highlighted
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?

Highlighted

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

Highlighted
Not applicable

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.

Highlighted

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

Highlighted
Not applicable

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?

Highlighted

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

HIC