Combined set analysis

[alex59800]

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

[MK_QSL · Accepted solution]

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)

[Aurelien_Martinez]

Hi,

I think this expression work :

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

[alex59800]

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

[MK_QSL]

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 !

[alex59800]

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

[MK_QSL]

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