Dynamic intersection of sets (Set Analysis)

[marwa_zerzeri]

Hello,

I want to create a  pie chart that displays the number of clients by type:

type 1- engaged customer

type 2- depositor client

type 3- engaged and depositor client

I created a measure count(distinct([CLIENT ID])) and a dimension type client :

=if(
[Type Ligne]='Depositor' ,'depositor client',
if ([Type Ligne]='Engaged' ,'engaged client'

))

How could i display the 3rd type "engaged and depositor client" knowing that a client can be depositor and engaged at the same time.

Thanks

[YoussefBelloum]

Hi,

how your data is structured ?

would you be able to share some lines here to see how your Type Line is presented ?

[marwa_zerzeri]

Hi,

The data is structured as described below:

CLIENT ID    TYPE LIGNE

1000              Depositor

1010              Engaged

1500             Depositor

1000             Engaded

2000            Engaged

=> On the pie chart, i must have 1 depositor, 2 engaged and 1 depositor and engaged

Thanks,

[Channa]

=only( {<[Type Ligne] = p({<[Type Ligne] = {'Depositor'}>})*p({<[Type Ligne] = {'Engaged'}>})>} [Type Ligne]  )

intersection

[YoussefBelloum · Accepted solution]

Hi,

I'm sure you can find better solution than this (on the script maybe), but at least it works:

chart: pie chart

dimension:

=ValueList('Depositor', 'Engaged', 'Depositor and Engaged')

Expression:

=if(ValueList('Depositor', 'Engaged', 'Depositor and Engaged')='Depositor and Engaged',

Count(distinct {<[CLIENT ID]= P ({<[TYPE LIGNE]={'Depositor'}>} [CLIENT ID] ) >
*<[CLIENT ID] = P ({<[TYPE LIGNE]={'Engaged'}>} [CLIENT ID] ) > } [CLIENT ID]),

if(ValueList('Depositor', 'Engaged', 'Depositor and Engaged')='Depositor',

count(distinct{<[CLIENT ID]= P ({<[TYPE LIGNE]={'Depositor'}>} [CLIENT ID] ) >
- <[CLIENT ID]= P ({<[TYPE LIGNE]={'Depositor'}>} [CLIENT ID] ) >*<[CLIENT ID] = P ({<[TYPE LIGNE]={'Engaged'}>} [CLIENT ID] ) >} [CLIENT ID]),

if(ValueList('Depositor', 'Engaged', 'Depositor and Engaged')='Engaged',

count(distinct{<[CLIENT ID]= P ({<[TYPE LIGNE]={'Engaged'}>} [CLIENT ID] ) >
- <[CLIENT ID]= P ({<[TYPE LIGNE]={'Depositor'}>} [CLIENT ID] ) >*<[CLIENT ID] = P ({<[TYPE LIGNE]={'Engaged'}>} [CLIENT ID] ) >} [CLIENT ID])

)))

[marwa_zerzeri]

It works. Thank you 🙂