Skip to Content
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 🙂

1 Comment
You must be Logged on to comment or reply to a post.