Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

Not applicable

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.

Tags (1)
1 Solution

Accepted Solutions
ramoncova06
Valued Contributor III

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

7 Replies
ramoncova06
Valued Contributor III

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

Not applicable

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

MVP
MVP

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

ramoncova06
Valued Contributor III

Re: Set Analysis - Get unique records

you mean something like this ?

Not applicable

Re: Set Analysis - Get unique records

That's excellent!! Thanks a mil.

Not applicable

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:

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 :

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

I really appreciate your help.

ramoncova06
Valued Contributor III

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

Community Browser