Set Analysis - Get unique records

[#1]

Hi guys

So in the table below, we have a maximum of 2 attempts to get a successful transaction per policy ID but not all first attempt failures are retried. I am trying to create a unique view that shows me distinct count of Policy ID's that were successful or unsuccessful as show in the second table. Please note that selections can still be made on the Success Flag on the sheet.

Policy ID	Attempt Number	Success Flag
1	1	No
1	2	Yes
2	1	No
3	1	No
3	2	No
4	1	Yes
5	1	No

Unsuccessful	Successful	Total
3	2	5

Your help will be greatly appreciated.

[ramoncova06 · Accepted solution]

see the attached file

my assumption was that you only wanted to exclude the ones that did not had a success flag

[#3]

Thanks Ramon . You are a genius and you just saved my life. Quick question: Would it be possible to create one expression and use the Success Flag as a dimension in a table? I need to do this for 6 different metrics and that would mean creating 18 expressoins

[maxgro]

successful

sum(aggr(count({$ <[Success Flag]={Yes}>} distinct [Success Flag]),[Policy ID]))

total

count(DISTINCT [Policy ID])

unsuccessful

total - successful

[ramoncova06]

you mean something like this ?

[#6]

That's excellent!! Thanks a mil.

[#7]

Hi Ramon

So it gets a little more complicated when I include the periods and types. We have an overlap of policies which should count for both successful and unsuccessful per period. Also the successful policies from [Policy ID] =E({1<[Success Flag] = {'Yes'}>}) should also consider that specific period in the dimension instead of the entire data set. So, with the following set of data:

load * Inline

[Period , Policy ID , Type , Attempt Number , Success Flag

A , 1 , X , 1 , No

A , 1 , X , 2 , Yes

A , 1 , Y , 1 , Yes

A , 2 , Y , 1 , No

A , 3 , Y , 1 , No

A , 3 , Y , 2 , No

A , 3 , X , 1 , Yes

A , 4 , X , 1 , Yes

A , 5 , X , 1 , No

B , 1 , X , 1 , No

B , 1 , X , 2 , Yes

B , 1 , Y , 1 , Yes

B , 2 , Y , 1 , Yes

B , 3 , Y , 1 , No

B , 3 , Y , 2 , No

B , 3 , X , 1 , Yes

B , 4 , X , 1 , Yes

B , 5 , X , 1 , No];

This should result in the following tables :

Period	Success Flag	Metric
		5
A	Yes	3
A	No	3
B	Yes	4
B	No	2

Period	Success Flag	Metirc
		4
A	Yes	3
B	Yes	4

Period	Success Flag	Metric
		2
A	No	3
B	No	2

I really appreciate your help.

[ramoncova06]

the easiest thing to do is create a combination of the policy + period and then filter based on that