Ensuring Validity of Time Filters After Time Labelling
We've recently released the ability to change the two-character prefix for time labels. If you have a hardcoded filter referencing a time formatting line item, it is possible that changing the Time Label will invalidate the filter. This occurs if the filter is referring directly to the name of the time period; changing the time label will, effectively, change the name of the time period, so the filter is then invalid. The good news is that following Best Practices you can prevent this, so it is best to check the filter(s) (and re-engineer if necessary) before changing the Time Label.
The following illustrates the issue and show the steps to prevent/resolve:
How the Issue May Occur
In the example, the model has a line item that is formatted as a time period—in this case, Quarter.
There is also a filter set on the line item, and the filter criteria is as follows:
From the above, you can see that the filter refers to a specific time period - "Q1 FY20".
The resultant module will look like this:
Now, let's change the time label in the Time Settings tab, and change the FY prefix as required.
The effect is that now Q1 FY20 is renamed to Q1 CY20. When the target module is refreshed, note that the filter is invalid and so returns all rows.
Click on the Filter icon, and you will see that the filter has been removed (because it was rendered invalid).
Click on the "–" symbol and re-create the filter.
The target module is now correctly filtered as before.
All good? Well, not quite.
As per the Planual, the above filter construct does not follow Best Practice as it is hardcoded.
There is an alternative method that conforms to Planual rules as it is sustainable, and it will also remain valid once the Time Labels have been edited.
Best Practice Approach
To ensure the filter remains valid, the filter structure should be changed before the Time Label is amended, so let's assume we haven't yet changed the label.
Set up a Time Filters module and a boolean line item (in the case called 'Filter'), dimensioned by the required time format (in this case Quarter).
Check the appropriate Quarter, as below.
In the target module (or a separate module), add a line item that will be used as the filter. Format this line item as a Boolean and add the following formula.
Time Filter.Filter[LOOKUP: Time]
Now replace the filter criteria to use the ‘Filter’ line item.
The result is the same as the initial filter.
Now that the filter structure has been adjusted, it is safe to change the Time Labelling.
In the Time setting tab, change the FY prefix as required.
The filter is still valid after the change of Time Label.
Following the above steps will ensure you can utilize the new Time Labelling functionality, while also adhering to Best Practices and ensuring your model filters remain valid.