Executing IF-THEN ELSE before Executing Calculation - Tableau

Question

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?

Answer 1

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