Showing Complete Set of Data with Filter and parameter - Tableau 10

Question

I have a set of data below.

| AdviserName | PolicyNumber | Product | Status | Duration |
|-------------|--------------|---------|--------|----------|
| Andy        | LIF123       | ANZ     | New    | 2        |
| Andy        | X224         | AXA     | Lapsed | 3        |
| George      | KL1122       | TAL     | New    | 0        |
| George      | OLK43        | AXA     | Lapsed | 5        |
| Ben         | LIF98        | ANZ     | New    | 0        |
| Ben         | KL343        | TAL     | Lapsed | 1        |

I want to get the set of data where the status = 'Lapsed' duration between 1 to 3. However I still want to show correspond status = 'New' for each adviser. Below is the expected result that I want to see.

| AdviserName | PolicyNumber | Product | Status | Duration |
|-------------|--------------|---------|--------|----------|
| Andy        | LIF123       | ANZ     | New    | 2        |
| Andy        | X224         | AXA     | Lapsed | 3        |
| Ben         | LIF98        | ANZ     | New    | 0        |
| Ben         | KL343        | TAL     | Lapsed | 1        |

I created 2 parameters :

• MinYear with the value set to 1
• MaxYear with the value set to 3

I created 2 calculated fields :

• MinInvYear which is iif([Duration]>=[Min Year] and [Status] = 'Lapsed', 1, 0)
• MaxInvYear which is iif([Duration]<=[Max Year] and [Status] = 'Lapsed', 1, 0)

Last step, I put both calculated fields into the filters and tick the filter = 1 only. Unfortunately what I got is the table below. I need to show the status 'New' as well for Andy and Ben.

| AdviserName | PolicyNumber | Product | Status | Duration |
|-------------|--------------|---------|--------|----------|
| Andy        | X224         | AXA     | Lapsed | 3        |
| Ben         | KL343        | TAL     | Lapsed | 1        |

I tried to modify the calculated field using Level of Detail :

MinInvYear which is {include [Status] = 'New Business' : iif([Duration]>=[Min Year] and [Status] = 'Lapsed', 1, 0)} and I got error message 'Result of a level of detail expression must be aggregate.'

What am I missing?

FYI, I'm using Tableau 10.

Thanks all for your help.

Answer 1

Instead of the 2 calculated fields MinInvYear and MaxInvYear, try adding a calculated field (let's say Filter for Status and Duration) which returns a boolean value based on your condition

IF ([Status] == 'Lapsed' OR ([MinYear] < [Duration] AND [Duration] < [MaxYear])) THEN
    TRUE
ELSE
    FALSE
END

Now, add a filter for this calculated field to show only records which result in True.

Answer 2

try this, Bit lengthy but works for sure:

You are manipulating on only one aspect (Lapsed) of the field status, hence you are applying the parameter values on only that value. So here you need to independently work on that column to get the required output. Follow the below process:

1. Create 2 sets for each value in status column:

Set 1: Lapsed

Here create a set and just select checkbox `Lapsed`.

Set 2: New

Here create a set and just select checkbox `New`.

2. Create two calculated fields to extract the required data separately for Lapsed and New

Calculated Field 1: Lapsed_Status

IF [Duration] >= [Min] and [Duration] <= [Max]
THEN [Lapsed] //Set
END

Calculated Field 2: New Status

IF [New]=[New]
THEN [New] //Set
END

3. Create two more sets out of 2 calculated fields (created in step 2) to just select only those values that are needed for display on sheet:

Set 3: Display_Lapsed //From Calculated Field 1: New Status

Select checkbox True

Set 4: Display_New //From Calculated Field 2: Lapsed_Status

Select checkbox True

4. Now we have our required data in 2 sets, Create one more calculated field and use in filter

Calculated Field 'Display_sheet'

Display_Lapsed <> Display_New //Both are sets

5. Use the calculated field Display_sheet in filter and select true

6. Use the required fields on the sheet to display data.

Let me know how it goes

Answer 3

Below trick will get you moving

• Duplicate the data source (right click on the data source in 'Data' panel -> 'Duplicate') having above data
• Drag AdviserName from the original data source in 'Rows' view
• Click on the duplicated data source in 'Data' panel. There you'll find 'link symbol' next to every dimension (in grey color). Click on the 'link symbol' next to AdviserName and it'll turn 'red' (if it turns grey don't worry. Click again and it'll turn red). Be sure that only one 'link symbol' is red (which is next to AdviserName).
• Create a calculated field filter_cond in this duplicated data source as

---

IF [Status]='Lapsed' and [Duration]<=3 and [Duration]>=1
then 'Y'
ELSE 'N'
END

• Drag filter_cond in 'Filters' view and select 'Y'
• Now drag PolicyNumber, Product & Status from the original datasource in 'Rows' view. You are done!

Voila!