Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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
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 ?
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?
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
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.
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
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?
Usually Set Analysis is faster - and more importantly - it is faster for large data sets, i.e. when you need the performance.
HIC