Week Over Week Comparison Demand Value Versus Booking Calculation Using Hana Live Connection
Ganesh Vijay Kumar Jun 27, 2018 9:49 AMHello 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