0 votes

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?

Sep 28, 2018 in Tableau 76 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
• 1,790 points

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

0 votes
1 answer

## Calculation issue: Distinct sum in Tableau

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

+1 vote
1 answer

## Show 0 if value has no data - Tableau

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

0 votes
1 answer

## Calculated filed with if-then

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

+2 votes
1 answer

## How can I download tableau workbook with its data from tableau server?

In essence, no. Since the workbook is ...READ MORE

0 votes
0 answers

## Executing IF_THEN_ELSE in Tableau

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

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

0 votes
1 answer

## Tableau Filter on field which contains MAX of another field

This is a good use case for ...READ MORE

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

0 votes
1 answer

## Which one should I choose Tableau or Power BI?

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