Filtering On Measures

[sreevatsa_adiraju]

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 🙂