Get value that is outside scope of filter

0
votes

I'm working on a tableau project with long format table data displaying start months, end months, and monthly costs. I'm trying to display a visual of a (dynamic) range of dates' monthly costs, and then overlay a reference line of the multi-month row's total cost. I suspect the answer lies in using a level of detail (LOD) expression but can't seem to figure this out.

example csv:

start,end,month length,x,a,b,c,d,total
1/1/2019,1/1/2019,1,1.2,0.08,0.01,0.1,0.299,0.489
2/1/2019,2/1/2019,1,1.1,0.08,0.01,0.1,0.295,0.485
3/1/2019,3/1/2019,1,0.9,0.08,0.01,0.12,0.285,0.495
1/1/2019,2/1/2019,2,2.3,0.08,0.01,0.1,0.297086956521739,0.487086956521739
2/1/2019,3/1/2019,2,2,0.08,0.01,0.109,0.2905,0.4895
1/1/2019,3/1/2019,3,3.2,0.08,0.01,0.105625,0.2936875,0.4893125

ignore x,a,b,c,d - they are to show that the chart has multiple measures equaling the monthly totals.

For identical start and end months, we have a month length: 1. The values associated with these rows are the monthly values. For graphing a dynamic range of months, I can graph these monthly values by filtering the month length: 1 and adding a calculated field filter for setting the start date to end date range [Start Date] >= [Selected Start Date] AND [Start Date] <= [Selected End Date]

Example Pic

Given this table, how might I show a reference line for the total value that exists for the start to end range selected (start date: 1/1/2019, end date: 3/1/2019, month length: 3), even though there is a filter of month length: 1? The end result here would be a line of 0.4893125

1
tableau-api

1 Answers

0
votes

Yes, you are correct, you need to use LoD.

If you need to bypass the filter then use LoD Fixed, below code snippet will give you the data for month = 3 though the filter is month=1. Change the code as per your requirements:

{ FIXED : Max(IF [Month Length] = 3 THEN [A] END)}

Edit----------------------------------------------------------------------

{ FIXED : 
Max(
IF ([Start Date] >= [Selected Start Date] AND 
[Start Date] <= [Selected End Date]) AND
[Month Length] = 3 THEN [A] END)}