topic Combined set analysis in QlikView

Combined set analysis

alex59800 — Mon, 20 Apr 2015 14:14:35 GMT

Hello

We have a simple set analysis as attached.

We would like to count in expression 1 : client with CA in 2015 > 300. Work well !

In expression 2 : we would like to count client with CA in 2015 > 300 and CA in 2013 < 2000. In that case, only Client Pierre is concerned as Client Jean has a CA = 300 in 2015. He should be excluded into the count calculation.

I use this set analysis below for expression 2, but not working....client jean is not concerned. Could you help me ?

=count({$<[Client]={"=Sum({$<Année = {2015}>} [CA] ) > 300"},[Client]={"=Sum({$<Année = {2013}>} [CA] ) < 2000"}>}distinct [Client] )

Thanks a lot

Re: Combined set analysis

MK_QSL — Mon, 20 Apr 2015 14:26:12 GMT

Expression 1

=COUNT(DISTINCT {<Client = {"=SUM({<Année = {'$(=Max(Année))'}>}CA)>300"}>}Client)

Expression 2

=COUNT(DISTINCT {<Client = {"=SUM({<Année = {'$(=Max(Année))'}>}CA)>300"}>*<Client = {"=SUM({<Année = {'$(=Max(Année)-2)'}>}CA)<2000"}>}Client)

You can fix Max(Année) by Max({1}Année)

Re: Combined set analysis

Aurelien_Martinez — Mon, 20 Apr 2015 14:28:56 GMT

Hi,

I think this expression work :

=count ( {$<[Client] = {"=Sum({$<Année = {2015}>}CA ) > 300 and Sum({$<Année = {2013}>}CA) < 2000"} >} distinct [Client] )

Re: Combined set analysis

alex59800 — Mon, 20 Apr 2015 14:36:32 GMT

Hello Manish

Seem to work well ! Thank you.

Why do you substitute Année by a variable $(=Max(Année)) ? Is there any interest or can i simply use 2015 and 2013 in your formula ? It seems to work also.

Best regards

Re: Combined set analysis

MK_QSL — Mon, 20 Apr 2015 14:57:02 GMT

because 2015 is the maximum date... Using the same application, if you want to compare 2016 and 2014 next year, you don't need to change the formula... But if you hard code it, you need to change it again..

Depends on what your requirement is !

Re: Combined set analysis

alex59800 — Mon, 20 Apr 2015 15:39:43 GMT

Ok thanks a lot.

One last question : what happen if i have no record in databasis for a client and i would like to know if it's a new client.

Example : Client Arthur is not existing at all before 2015 and just has an occurence in 2015.

=COUNT(DISTINCT {<Client = {"=SUM({<Année = {2015}>}CA)>0"}>*<Client = {"=SUM({<Année = {2013,2014}>}CA))=0"}>}Client)

not working because no records for this client in 2013 and 2014. I tried to find out something with isnull in this set analysis but not found. If you have an idea would be great !

Best regards

Alex

Re: Combined set analysis

MK_QSL — Mon, 20 Apr 2015 18:57:12 GMT

You need to assign each client with each year (i.e. Annee) in script.