Optimal way to calculate ratio in SAP HANA

[Former Member]

Hi Experts,

Lets say we have Revenue information Per Customer/product/Sold-To as shown below

Now we need to find the ratio of allocation for each sold-To as shown below. What is the best way to find that would be ?

One of the way, which i can think of create an aggregation node on the Revenue table just with Customer and Product and make Amount as Sum for aggregation type.

Later join that with the same table to do the mat of amount/Total Amount to get to the ratio.

The idea is later this ratio will be used to disaggregate the data from Customer to Sold-To level.

Thanks,

Jomy

[former_member200930]

Hi Jomy,

Go for Dynamic join

Proposed Solution :

Agg Node 1 (Total) :

1. Customer
2. Product
3. Sum(Amount) Rename This column to "Total"

Agg Node 2 (Line item data) :

1. Customer
2. Product
3. Sold-To
4. Sum(Amount) as Amount

Create join between Node1.Customer = Node2.customer and node1.Product=Node2.Product and set dynamic join property on.

Do not create join on Sold-to

Create calculated column as "Ratio" = Node2.Amount / node1.Total.

This will be another approach. Try it.

Happy learning

-Vikram Divekar

[Former Member]

Hi Vikram,

How my solution is different from yours?

The only difference between your model and mine is you are also setting Dyamic join property as True but going with simple join on Customer and Product.

What are we gaining by just setting the dynamic join as true ?

Thanks,

Jomy

[former_member200930]

Dynamic join is one of the a smart join. It will take care total again line item data for particular attribute. (Sold-to etc).

[michael_eaton3]

Hello

Yep, your solution will work.  Did you try it?  It would be quicker to build the model than write the question!

Michael

[Former Member]

Hey Micheal,

Yes the solution did work but thought to check experts like you to know is there some better way or not 🙂

Thanks,

Jomy

[michael_eaton3]

We would never say better, merely different solutions.

If you show your model, we can see how good it is! 

Michael