Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in BI by (17.6k points)

I have a graph below.

enter image description here

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. 

1 Answer

0 votes
by (22.5k points)
edited by

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

 

For more learning, join our Tableau Training

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

30.5k questions

32.6k answers

500 comments

108k users

Browse Categories

...