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
Last Modified Date: 20 Jun 2017

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.

Additional Information

{FIXED} Level of Detail Expressions take precedence over Dimension Filters and Measure Filters, and can thus yield unexpected results. However, the above Level of Detail calculation is not being used directly in the view. Because the calculation is only used to filter the view, the calculation will not cause data to be added to the view that should be filtered out by other filters in use.
Did this article resolve the issue?