Personal Insights
Filtering On Measures
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:
| Employee | Benefit Code | Benefit | REV | NEW Benefits |
| 1 | XYZ | -100 | 1500 | Same as Benefits since Benefits not equal to ZERO |
| 2 | ABC | 200 | 2200 | Same as Benefits since Benefits not equal to ZERO |
| 3 | PQR | 300 | 2800 | Same as Benefits since Benefits not equal to ZERO |
| 4 | XYZ | 100 | 1100 | Same as Benefits since Benefits not equal to ZERO |
Actual Result: if we use Benefit code in the Joins, filter will change Dynamically
| Benefit Code | Benefit | Salary | NEW Benefit |
| XYZ | 0 | 1500 | New Benefit will be calculated which is wrong |
| ABC | 200 | 2200 | Same as Benefits since Benefits not equal to ZERO |
| PQR | 300 | 2800 | Same as Benefits since Benefits not equal to ZERO |
‘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.
| Employee | Benefit Code | Benefit | Flag( True if Benefits not EQ ZERO ) | Salary |
| 1 | XYZ | -100 | T | 1500 |
| 2 | ABC | 200 | T | 2200 |
| 3 | PQR | 300 | T | 2800 |
| 4 | XYZ | 100 | T | 1100 |
To Summarize, we cannot keep filters on Measures, values will change dynamically depending on attributes we use. To achieve this we have to create a calculated column(flag) with the required filter expression at initial node level itself and use this flag in filters In higher nodes.
Happy Reading 🙂
Nice blog. Good work !