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 28, 2018 in Tableau by Atul
• 10,240 points
45 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 28, 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 Atul
• 5,530 points
154 views
0 votes
1 answer

Calculation issue: Distinct sum in Tableau

SUM( { FIXED [Brand], [Market]: AVG(Target) } ...READ MORE

answered May 8, 2018 in Tableau by Atul
• 10,240 points
2,861 views
0 votes
1 answer

Show 0 if value has no data - Tableau

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

answered Apr 24 in Tableau by Cherukuri
• 31,840 points
1,624 views
0 votes
1 answer

Calculated filed with if-then

Below is the required code:  IF (NOT ISNULL([test2])) ...READ MORE

answered Mar 27, 2018 in Tableau by Atul
• 10,240 points
98 views
+2 votes
1 answer
0 votes
0 answers

Executing IF_THEN_ELSE in Tableau

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

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

edited Aug 14, 2018 by Atul 38 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 24, 2018 in Tableau by Atul
• 5,530 points
303 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
2,206 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 5, 2018 in Tableau by ghost
• 1,790 points
74 views