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:

       

      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

      Capture.PNG

       

      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

      Capture3.PNG

       

      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:

       

      Capture12.PNG

       

      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