KB483350: Conditional metric shows nothing when aggregated

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Symptoms

• No data for a conditional metric which is aggregated, e.g. totals
• Applying the NullToZero function makes no difference.
• Depending on the version of Strategy, either:
  
• Empty cells
• Blank KPIs
• An error message ("No data returned for this view. This might be because the applied filter excludes all data")

Glossary

• Conditional metric - a metric with a filter applied to it
• Null - value that represents a non-existent, missing, or zero value

Steps to Reproduce

• Create a new Dossier.
• Click "Existing Objects" and add the Item attribute and Revenue metric.
• Create a new metric, Count(Revenue) with the following definition:

• Note: We set a filter with Revenue = 10000 to filter out all data points because there is no Item with this Revenue value. You may pick any value that does not exist within the dataset to achieve the same effect.
• Create a new metric NullToZero(Count(Revenue)) with the following definition:

• Add a grid visualization and drag Count(Revenue) to the Metrics section.
• Add a KPI visualization and drag Count(Revenue) to the Metrics section. 
• Add a KPI visualization and drag NullToZero(Count(Revenue)) to the Metrics section.

Results:

Cause

• Condition in metric filters out all data in fact tables 
• After aggregation, the metric returns nothing, so nothing is displayed.

Solution

This is fixed in Strategy 10.10 and above. To resolve this defect:

• Upgrade to Strategy 10.10 or above. We recommend upgrading to the latest version, Strategy 2019.
• Set Data Engine setting to 11. This is not set by default.
  
• The project level setting Data Engine can only be enabled through Workstation. For more details on enabling the setting, see Change Data Engine Version.

In the new fix, when a conditional metric is aggregated to yield one value and its filter causes all data to be filtered out, it will return some data, either a null or blank value. If the NullToZero function has been applied, it will return 0.
Results after fix:

 
 
The Strategy Internal Reference Number for the issue discussed in this technical note is KB483350 and DE75549.