Executing IF-THEN ELSE before Executing Calculation - Tableau

0 votes

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 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 by Atul
• 10,240 points
858 views

1 answer to this question.

0 votes

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
answered Sep 29, 2018 by ghost
• 1,790 points

Related Questions In Tableau

0 votes
1 answer

Do I need a Tableau Server if I already have a Tableau Desktop?

Tableau Desktop which need a license.. Tableau Server ...READ MORE

answered Apr 20, 2018 in Tableau by ffdfd
• 5,550 points
980 views
0 votes
2 answers

Calculation issue: Distinct sum in Tableau

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

answered Aug 26, 2020 in Tableau by Dhiraj Kumar Sahu
9,328 views
+1 vote
4 answers

Show 0 if value has no data - Tableau

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

answered Apr 24, 2019 in Tableau by Cherukuri
• 33,030 points
122,545 views
0 votes
1 answer

Tableau - What would the LOD calculation be?

Based on your requirements, I don't feel ...READ MORE

answered Mar 3, 2022 in Tableau by Vaani
• 7,060 points
506 views
0 votes
1 answer

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

answered Apr 5, 2022 in Tableau by Neha
• 9,060 points
928 views
0 votes
0 answers

Executing IF_THEN_ELSE in Tableau

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

Aug 14, 2018 in Tableau by Atul
• 10,240 points

edited Aug 15, 2018 by Atul 532 views
0 votes
1 answer

Tableau Trend Line with Date Field Calculation

Try This: if Date= today() then 1 elseif dateadd('day',- day(DATEADD('month',1,Date)),DATEADD('month',1,Date))=Date ...READ MORE

answered Aug 25, 2018 in Tableau by ffdfd
• 5,550 points
1,332 views
0 votes
1 answer
0 votes
1 answer

How to color code cells of a column based on the text value in Tableau

You can use the following steps to ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
9,553 views
0 votes
1 answer

Which one should I choose Tableau or Power BI?

Hi Grim, Both the BI tools - Tableau ...READ MORE

answered Apr 6, 2018 in Tableau by ghost
• 1,790 points
707 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP