Hello Experts,

I am trying to create a calculation and stuck in between. Hope any one of could guide me to arrive at the desired results.

Let me explain the "Dimensions" and Measures" I am using to calculate:

Dimensions:

1) Customer Request Ship Calendar Week:(This is year + Week)

201826

201825

2) Week Difference: This is help me to Identify Current week; Previous week and future week

201825 1 : Previous week

201826 0 : Current Week

201827 -1 : Future Week

Measures:

1) Order Quantity

2) Demand Quantity

Result I am trying to achieve is as below

Order Number : 123456

Lead time : 6

Since the lead time is 6 I have to get the previous 6 weeks demand Quantity and compare it with "Current Month" Order Quantity.

Image 1.0

Each and every week is compared to Week 0

E.g.  week 6 "Demand Qty" = 50,400 - Week 0 "Order Qty" 2400 = -48000/54000 = -95%

My result should be = If each and every week demand qty = Order quantity Then " We are Liable" else "Customer is Liable"

In the above example as shown in image 1.0 the result would be "Customer is liable"

Image 2.0

In the above example as shown in image 2.0 the result would be "We are liable"

Image 3.0

In the above example as shown in image 3.0 the result would be " Customer is liable" since demand qty for each week is not matching with order qty.

Formulas I created to arrive at the results.

Cal 1) Identifying the Previous weeks : We have separate "Table" which gives us lead time by order.

For Eg : Order number : 123456 Lower Limit 1 and Upper Limit 6

Formula I wrote to filter pvs 6 weeks.

Period Change:

ATTR([Week Diff)]) >= Avg([SAP_HANA_req (CSSC)].[(period 1) Lower limit week])

AND

ATTR([Week Diff]) <= AVG([SAP_HANA_req (CSSC)].[(period 2) Upper limit week])

Cal 2) To get Demand + Order Data : Since we have demand in one table and Order data in another table. Below is the formula I wrote.

Final Value (Booking+Demand):

IF ATTR([Week Diff]) = 0 THEN SUM([Custom_SQL_Query (_SYS_BIC) Order].[Order Quantity])

ELSE SUM([Demand Qty]) END

Cal 3) To calculate Deviation I Used Table Calculations:

Final Value Deviation:

Results are computed along Table (across)

(- ZN([Final Value (Booking+Demand)]) + LOOKUP(ZN([Final Value (Booking+Demand)]), FIRST())) / ZN([Final Value (Booking+Demand)])

Cal 4) To calcuate Decision Statement:

IF ATTR([Week Diff]) = 0 THEN

    "Incident Week"

ELSEIF

[MIN Deviation %] <> 0

AND

[MAX Deviation %] <> 0

THEN

'Customer Liable'

ELSE

'We are Liable'

END

Below are the final results:

It is working fine and providing me the desired output.

However the problem is when I want to see the decision at order level without using "Customer Request Ship Calendar Week" in Rows Shelf.

Say for Example I want to make the decision at order Level

Order number  Decision

123456             Cust is liable

I am not able to achieve since I am using Table calculations and getting the % values. Unfortunately LOD expressions are not supported in Hana live connection. Hence I cannot use "Include" functions as well.

I am stuck here and clueless on how to achieve the desired results. Any help to achieve the results without using table calculations is deeply appreciated.

Hoping for a positive response!!!

Thanks,

Ganesh