I have a graph below.

I would like to calculate lapsed rate which is sum of lapsed value divided by sum of inforce value. I use the formula below in calculated field.

```abs(sum(if[Status]='lapsed'then[TotalAmount]end))/abs(sum(if[Status]='inforce'then[TotalAmount]end))
```

However, that formula will also pick the value from Q2 (quarter 2) 2016. What I want to do is to tell tableau to check first if any quarter does not contain both enforce value and lapsed value then skip that quarter. In this case, I need to calculate lapsed rate which does not include Q2 2016. How do I do this?

Sep 29, 2018 in Tableau 989 views

## 1 answer to this question.

You can use a combination of a row level calculation and a level of detail calculation. The level of detail calculation can be used to flag the quarters which have both a lapsed and inforced status. Once these quarters are flagged you can calculate the lapsed rate at a row level which can then be rolled up using a sum.

Create a calculated field as follows: Let me know if you have any questions or need any tweaks.

``` if
avg(
// Calculate the number of Inforce/Lapsed occurences per Quarter
IF
[Status] = 'Inforce'
or
[Status] = 'Lapsed'
then
{ FIXED
DATEPART('quarter', [Date]):
countd([Status])
}
else
0
end)
//
= 2
then
// Calculate the Lapsed Rate as both statuses exist in the quarter
sum((if
[Status] = 'Lapsed'
then [Total Amount]
END))
/
sum([Total Amount])

END
```
• 1,790 points

## Calculation issue: Distinct sum in Tableau

I feel the easiest way is to ...READ MORE

+1 vote

## Show 0 if value has no data - Tableau

Hi Abhay, To replace null or empty values ...READ MORE

## Is it possible to create user defined functions in Tableau? If yes, what coding language it uses?

Tableau does not allow you to construct ...READ MORE

## Executing IF_THEN_ELSE in Tableau

I have the following graph: I would like ...READ MORE

## Tableau Filter on field which contains MAX of another field

This is a good use case for ...READ MORE