topic Re: Gap between "if" and set analysis in QlikView

Gap between "if" and set analysis

Mon, 23 Jun 2014 12:17:57 GMT

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.

Re: Gap between "if" and set analysis

hic — Mon, 23 Jun 2014 12:22:25 GMT

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

Re: Gap between "if" and set analysis

Mon, 23 Jun 2014 12:30:44 GMT

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 ?

Re: Gap between "if" and set analysis

tresB — Mon, 23 Jun 2014 12:55:23 GMT

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?

Re: Gap between "if" and set analysis

hic — Mon, 23 Jun 2014 13:15:00 GMT

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

Re: Gap between "if" and set analysis

Mon, 23 Jun 2014 13:49:23 GMT

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.

Re: Gap between "if" and set analysis

hic — Mon, 23 Jun 2014 14:17:27 GMT

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

Re: Gap between "if" and set analysis

Mon, 23 Jun 2014 14:48:59 GMT

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?

Re: Gap between "if" and set analysis

hic — Tue, 24 Jun 2014 15:33:53 GMT

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

HIC