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!