Persistency in Tableau

1
votes

I am trying to calculate Persistency in Tableau report.

Persistency: This is a measure of how efficiently an adjuster (or claims office) is working through their claims. The calculation is simple:

Number of Claims, ( OPEN at Beginning of Month – OPEN at End of Month ) / (OPEN at Beginning of Month) as a Percent.

So if an adjuster has 100 open claims on 4/1, 10 new claims are opened in April, and adjuster closes/settles 40 claims, leaving 70 open on 4/30. Persistency = (100-70)/100 = 30%

I have datasheet with Claim Numbers and their respective "Date Of Report" as well as their "Close Date". I am confused how to proceed. Any help will be appreciated.

1
dashboardcalculated-fieldcalculationtableau-api
could you please provide any dataset sample? – Fabio Fantoni
Are any of the claims still open? Do they have a null value for the Close date? – Alex Blakemore
Hi @alex, Yes there are many claims still open with future close dates. Those claims have to be included. – user9066703
How Is a future close date represented? As a null value? – Alex Blakemore
Yes, They have been assigned a future date for example 12-31-9999 – user9066703

1 Answers

0
votes

I hope the Data looks similar to this Data Structure

With this let us try to find the Open Claims for Previous month and Open claims for current month To find the previous open claims, I have used the following code

if 
MONTH([Current Date])<>MONTH([Report Date])
and
[Status]="Open" then 1 end

For current open claims

if 
MONTH([Current Date])=MONTH([Report Date])
and
[Status]="Open" then 1 end

Now, with this, let us try to calculate Persistency

(SUM([Previous Open Claims])-SUM([Current Open Claims]))/SUM([Previous Open Claims])

Hope this works! Please let me know if you have more clarification!