When creating a calculated column (within an analytic- or calculation view) via the SAP HANA studio, unexpected behaviour was detected….after several hours of debugging. Herewith a small blog entry on what happened and how it was solved.
After having extracted the VBAP (sales order items) table to HANA, this VBAP table was used as data foundation layer within an analytic view. Besides (re-)using standard VBAP fields, several ‘new’ calculated fields were added.
Calculated field ZSKU_QTY was added to convert the sales unit quantity back to its base unit (=SKU) by multiplying the sales unit quantity with the conversion factor.
(Eg. ZSKU_QTY = KWMENG * ( UMVKZ / UMVKN) ).
Even though all fields in the calculation have a value (as to be seen in the screen grab above), the outcome of this calculation (ZSKU_QTY = KWMENG * ( UMVKZ / UMVKN)) resulted in a 0.000 value.
When executing the calculation directly via the SQL editor, the outcome of this calculation was as expected.
(Eg. CALC_QTY = KWMENG * ( UMVKZ / UMVKN) ).
Even though the calculation method in the analytic view corresponds with the calculation method in the SQL editor, the outcome of both calculations was unfortunately (=understatement) not the same.
It took a while before the cause of this anomaly was found, but finally the calculated column of the analytic view generated the same result as the SQL editor:
What eventually did the trick was adding the float function. This float function ensured the intermediate result, of the UMVKZ / UMVKN calculation, no longer to be equal to zero. Multiplying the KWMENG with this filled intermediate result, caused the calculate column ZSKU_QTY to contain the correct value.