topic Re: Set Analysis with AND condition in App Development

Set Analysis with AND condition

Neha_2227 — Sat, 20 Nov 2021 15:49:58 GMT

Hi Team,

In my dataset there is 1 flag which has values Y and N. There are few customers in Y and few in N but there are common customers as well which has both Y and N status. I want to show the common customers. Is it possible to achieve in front end with set analysis.

I tried this expression - Sum({<Doubtful={'Yes'},Doubtful={'No'}>}Amount)

This is giving me all the customers not the customers that have both doubtful Y and N.

Any suggestions to do it in front end.

Thank you

Best Regards

Neha

Re: Set Analysis with AND condition

abhijitnalekar — Fri, 19 Nov 2021 04:38:40 GMT

Hi @Neha_2227 ,

Please give it a try with below expression

sum(if( Doubtful='Yes' and Doubtful='No' ,Amount))

Re: Set Analysis with AND condition

Neha_2227 — Fri, 19 Nov 2021 06:30:43 GMT

This wont work as Doubtful field doesn't have both values together, either it has Yes or No at time.

Re: Set Analysis with AND condition

abhijitnalekar — Fri, 19 Nov 2021 06:33:58 GMT

Hi @Neha_2227 ,

Got it...

Is it possible to share the sample data? want to check if my second logic works or not

Re: Set Analysis with AND condition

NitinK7 — Fri, 19 Nov 2021 08:55:25 GMT

try below expression

Sum({<Doubtful={'Yes'} > * <Doubtful={'No'}>}Amount)

Re: Set Analysis with AND condition

Neha_2227 — Sat, 20 Nov 2021 15:50:39 GMT

Here you go

Re: Set Analysis with AND condition

Vegar — Sat, 20 Nov 2021 23:54:03 GMT

As you pointed out, each row only have Yes or No. An intersect between the two will not give any result. Consider looking at implicit set analysis using E(). I will look something like this:

Sum({<CustomerId =P({<Doubtful={'Y'} > } CustomerId) * <CustomerId =P({<Doubtful={'N'} > } CustomerId)>}Amount)

Re: Set Analysis with AND condition

NitinK7 — Mon, 22 Nov 2021 09:06:41 GMT

Hi,

Please find the attached .QVF file

Might be this will help you.