KNOWLEDGE BASE

## Unable to Filter Out Values of a Dimension for Which Another field is Zero, Null, or Missing for the Most Recent Time Interval

Published: 01 Dec 2016

### Issue

When information is being displayed along a dimension across time, you are unable to remove those values of the dimension along which the most recent value of a field is missing, null, or zero, using a Level of Detail calculation.

### Environment

• Tableau Desktop
• Excel

### Resolution

1. Create a calculated field containing the following:
```//for each value of [dimension]
{FIXED [dimension]:

MAX(

//check if the most recent time interval for a given value of [dimension]
//is the most recent time interval for all values of [dimension]
{EXCLUDE [time interval]: MAX([time interval])} =
{EXCLUDE [time interval],[dimension]: MAX([time interval])}

AND

//check if there is a time interval that is the most recent time interval
//in which [field] is non-null and not zero
[time interval] = {EXCLUDE [time interval]: MAX([time interval])} AND ZN([field]) != 0

)

}

//the outer MAX() aggregation is important for the second half of
//the calculation, because
//[time interval] = {EXCLUDE [time interval]: MAX([time interval])}
//may not be True for every [time interval] at a given value of [dimension]```
2. Replace [time interval] in the calculation with the period of time in your view. For example: MONTH([date]).
3. Replace [dimension] with the dimension to filter.
4. Replace [field] with the name of the field to check for zero, null, or missing values.
5. Drag the calculated field to the Filters Shelf and click "True".

### Cause

It is necessary to calculate at different levels of detail from the view level of detail, and consequently Level of Detail Expressions are required.