2 Replies Latest reply on Jul 10, 2018 6:54 AM by Ganesh Vijay Kumar

    Week Over Week Comparison Demand Value Versus Booking Calculation Using Hana Live Connection

    Ganesh Vijay Kumar

      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:




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




      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




      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

      Capture 2.PNG

      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])


      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"


      [MIN Deviation %] <> 0


      [MAX Deviation %] <> 0


      'Customer Liable'


      'We are Liable'



      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!!!