7 Replies Latest reply: Jun 9, 2015 11:07 AM by Ramon Covarrubias

# Set Analysis - Get unique records

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 IDAttempt NumberSuccess Flag
11No
12Yes
21No
31No
32No
41Yes
51No

UnsuccessfulSuccessfulTotal
325

Your help will be greatly appreciated.

• ###### Re: Set Analysis - Get unique records

see the attached file

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

• ###### Re: Set Analysis - Get unique records

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

• ###### Re: Set Analysis - Get unique records

you mean something like this ?

• ###### Re: Set Analysis - Get unique records

That's excellent!! Thanks a mil.

• ###### Re: Set Analysis - Get unique records

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:

[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 :

PeriodSuccess FlagMetric
5
AYes3
ANo3
BYes4
BNo2
PeriodSuccess Flag Metirc
4
AYes3
BYes4
PeriodSuccess Flag Metric
2
ANo3
BNo2

• ###### Re: Set Analysis - Get unique records

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

• ###### Re: Set Analysis - Get unique records

successful

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

total

count(DISTINCT [Policy ID])

unsuccessful

total - successful