How to calculate Average Price

[former_member187137]

Hi,

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:

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!

thanks,

gerhard

[Former Member]

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

Tick "Calculate before aggregation" to calculate each row before aggregating a play with that, if I get it right what the problem  is.

[former_member187137]

OK, so the calculation is [Sales]/[Quantity] which I actually want to be calculated as SUM([Sales]) / SUM([Quantity]) on aggregated levels

ticking "Calculate before aggregation" would do a SUM([Sales]/[Quantity]) and this is exactly what I do NOT want as it produces wrong results.

once i tick "Calculate before aggregation" I have to choose an aggregate function - SUM, MIN, MAX, COUNT of which none produces correct results on aggregated levels

[Former Member]

Ok, now I read whole correspondation .

I would turn my attention to SQL, some of the things encapsulate in few Views and reuse them in my code.

I'll try to figure something out,  this use-case seems interesting, but don't hold your breath...

In the mean time if You have Table DDL and table inserts with examples of correct results, it would be helpfull, maybe someone jumps into it regardless if it is important to you or not any more, as I said its fun example excersize if it can or not be achieved.

Thx

BTW, I dont know why everybody is running away from SQL, I know that it was frowned upon as Hana SQL optimizer was "not as good" , but now it seems more then ok. Or am I wrong ?

[pfefferf]

Hello Gerhard,

for your specific example you can solve this by using a calculation view with an aggregation an a union node.

Here a "quick and dirty" overview:

What steps I did:

• Create a projection node which gets the data from the DB table.
• Create an aggregation node which gets all products from the previous projection node. The Sales and Quantity information is summarized over all products in that node. Via a "calculated" column I set the pseudo product name "All Products".
• Create a Union node which unions the not aggregated product information from the first projection and from the aggregation node. The pseudo "product name" is mapped to the product name column in the output.
• In the projection node (that one before the semantics node) I added the calculated column for the average price. So the average price is calculated for the not aggregated and the aggregated values.

Here is the result:

Of course if you need some additional attributes beside the product name, they still have to be added to the attribute list.

Best regards,

Florian

[former_member187137]

Hi Florian,

thanks for your quick response. However, that's exactly what I was talking about. This solves the issue of grand/sub-totals on the product level but on the product level only.

Consider a data model/analytical view where I have 10+ dimensions/attribute views with 10+ columns each. I do not know in advance which attribute the user might use when browsing my model. He may wants to see the average price across months, regions, sales-reps, ...

The question is how to solve this issue - again, its basically a simple division on aggregated level which should be done AFTER all aggregations (SUM, COUNT, MIN, MAX)

thanks,

gerhard

[pfefferf]

Hello Gerhard,

thanks for clarification. Now I got your problem.

From my point of view this is not a requirement which has to be solved directly by the model itself, but from the consumer. Only the consumer knows over which attributes the aggregation should be done for e.g. the "sum" line.

In the context of AdHoc reporting using information views you will have a consumer tool. For example Analysis for Office or Lumira out of the BO Universe. I do not think that a business end user will directly use the information view itself. With Analysis for Office you consume the Attributes and Measures you wanna have and define if for example an additional aggregated line should be displayed.

Best Regards,

Florian

[former_member187137]

Hi Florian,

thanks again for the clarification on this.

Thing is that Average Price is a very simple calculation which, i guess, every business user is capable of defining. But there are other, more complex calculations like DSO (Days Sales Outstanding). Even though a business user may know how to calculate it, different business users may implement it differently. So it would make sense to define the calculation already in the model. This way you ensure that all users use the same calculation and it is consistent throughout the whole company as it is only defined once.

Defining the calculation on a report level may look convenient at first sight but once you have to change 100+ reports because the definition of your calculation changed you will probably think different about this.

what are your thoughts about this?

thanks,

-gerhard

[pfefferf]

Hi Gerhard,

I absolutely agree with that. That calculations can/should of course be done in the model. My point is that the consumer decides finally on which level the aggregation is done. The model provides such calculations on maybe a very granular way and the consumer decides about the aggregations.

Best Regards,

Florian

[former_member226419]

Hi Florian,

Can you please tell me how you have created psuedo value 'AllProducts' in model?

I just tried to replicated the same as you told above but I am not able to do the same.

BR

Sumeet

[pfefferf]

Hi Sumeet,

I just created a calculated column which sets the 'All Products' value. This calculated column is mapped in the Union to the "real" product name column as it can be seen above in the screenshot.

Of course this does not consider any language settings in that quick and dirty approach.

Best Regards,

Florian

[former_member187137]

Hi Florian,

predefining complex business calculations according to company's definition in the model however does not hinder the user to create his own calculations. But in this case the user is responsible for his calculation which may differ from the companies definition. As long as all source columns/measures are visible to the user you do not limit the end user in any way.

But coming back to the original question, we can say that it is not possible to define a universal average/ratio/... calculation in an analytical view - correct?

-gerhard

[pfefferf]

Hi Gerhard,

to my knowledge, no.

Best Regards,

Florian

[former_member187137]

Hi Florian,

thanks a lot for all your feedback, it really helped me a lot to understand the capabilities of HANA and how it compares to other technologies in terms of how and where to implement business logic

regards,

-gerhard