2 views
in BI

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 inforce 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?

I'm using Tableau v.10.

Thanks.

by (22.5k points)
edited

Try using this calculated field

``` 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])

```