Hello Friends,
This Blog is related to Filter on Measures in HANA view. In Ideal case, only Attributes are used in filters, today will discuss how measures can be used for filtering data in real time scenario.
Requirement:
Filter HANA view based on Measure, if Measure Not equal to Zero Move data to higher node, otherwise use in some calculation.
Business Case :
If Employee benefits not equal to zero propagate same value to higher nodes, if Employee benefits equals zero, calculate Benefit using Salary and AVG Benefit Rate
Consider we have Calculation view based on another calculation view(base) as source to get Employee Benefits for ZERO Benefit measures.
If we keep filter expression as below in base calculation view to get line items with Employee Benefits not equal to ZERO
Above filter expression makes aggregated Employee benefits value to give wrong results as its value will change dynamically depending on Attributes used in further joins.
Expected Result:
Actual Result: if we use Benefit code in the Joins, filter will change Dynamically
‘New Benefits’ should not be calculated for XYZ line item. Now it is aggregating -100, 100 giving ZERO, satisfying filter condition, so ‘New Benefits’ is calculated.
What Can be done ?
Instead of filtering measures value directly, create a new Calculated Column(named Flag here) in the base calculated view before all its aggregation nodes, propagate to higher view and keep filter on flag. Since we are keeping filter on attribute, it will not change Dynamically giving correct results.