Skip to Content
Apr 30, 2015 at 04:46 PM

How to calculate Average Price



having worked a lot with other analytic engines (mainly Microsoft Analysis Services) I am currently digging into SAP HANA and its Analytical Views. But I am already stuck at what I consider the most basic requirement - calculating an average like this:

Sales Quantity Avg Price Product A 1000 10 100 =1000 / 10 Product B 2 2 1 =5 / 1 All Products 1002 12 83,5 =1002 / 12 =1000 + 2 =10 + 2

So "Avg Price" is defined as "Sales"/"Quantity":

How can I make the "Avg Price" for 'All Products' show the correct value as in the example above?

at the moment it simply sums up 100 + 1 which is of course wrong as averages cannot be summed up ...

I have found some "solutions" (I would not call it like that) here on the SAP HANA Developer Center which all rely on creating a second table with the same granularity as the main table but without e.g. products and use this to create averages, ratios, etc.

see for example here: SAP HANA: Using "Dynamic Join" in Calculation View (Graphical)

But this does of course not work anymore if i put e.g. Months or any other attribute (except Products) on rows which renders the whole Analytical View useless for Ad-Hoc reporting

maybe I just missed something (hope so)

any feedback is highly appreciated!




AvgPrice.jpg (140.0 kB)