1 Reply Latest reply on Sep 13, 2018 2:37 PM by Patrick A Van Der Hyde

    NET OR ABS value using LODs

    Ganesh Vijay Kumar

      Hello Experts,

       

      I am trying to calculate Net or ABS value using Parameter control. I have hit a roadblock and hope any of you would guide me in the right direction.

       

      Just to give little bit of background of the data set I am working on. It is 3 dimensional and weekly snapshot based data.

       

      Basically I am trying to compare difference between each snapshot.

       

      Below are the dimensions Available in the data set:

      Worldwide number

      Worldwide Name

      Sold To Name

      Customer Number

      PN

      Customer Request Date Week Id

      Customer Request Date Year Id

      Report date

      Week Diff

      Region_Name

       

      Below are the Measures Available in the data set:

      SumOfBacklog Amount - C$

      Backlog Quantity

       

      Capture1.PNG

       

      As shown in the image above I created a cross tab.

       

      I have used Worldwide number, Worldwide Name, PN in column shelf and week diff in the column shelf

       

      Week diff is calculated at database level. Under Week diff 0 = Final call off ; 1= I week before 2= 2 weeks before .... etc...

       

      I am trying to calculate the difference as shown in the below image. 0 is base and calculate the difference verses week diff 2,3,4 etc....

       

      To achieve the desired results I Created few Parameters and calculated Fields as follows.

       

      Week and Duration FIlter:

      [Weekdiff] <= [Duration in Weeks] AND [Weekdiff]>=0

       

       

      Final Call Off

       

      { EXCLUDE [Weekdiff]: SUM(If [Weekdiff] = 0 THEN  [Backlog Quantity] END)}

       

      Gap/Variance

       

      { INCLUDE [PN]:

      CASE [Gap Type]

          WHEN 'Net' THEN ZN(SUM([Final Call Off]))-ZN(SUM([Backlog Quantity]))

          WHEN 'Absolute' THEN ABS(ZN(SUM([Final Call Off]))-ZN(SUM([Backlog Quantity])))

      END

      }

       

      The ABS/NET calculation works absolutely fine when I have PN in rows shelf. The moment I remove PN and keep only week the variance/gap is incorrect.

       

      For eg: lets filter by only one PN: As shown in the below image The Gap for week 1 =-600 and week 2=600 and the Grand total is 0 which is absolutely correct.

       

       

      In the below I change the parameter to Absolute, As shown in the below image The Gap for week 1 =600 and week 2=600 and the Grand total is 1200 which is absolutely correct.

       

       

      Now I will remove the PN from rows shelf, since I have to see numbers at only week level and all the PN' together.

       

      53600-32200 = 21400 but I am getting 22600. After doing bit of a research I got to know 22600 =600+18000+4000

      The absolute value calculations should be at various dimensions mentioned above. But with the calculations I wrote so far is giving me results only when Include PN.

       

      Hope I am very clear in explaining what I am trying to achieve. I have also attached a sample twbx file for your reference.

       

      Any help in achieving the results is deeply appreciated.

       

      Thanks,

      Ganesh

        • 1. Re: NET OR ABS value using LODs
          Patrick A Van Der Hyde

          Hello Ganesh,

           

          Change Gap to

           

          { INCLUDE [PN]:

          CASE [Gap Type]

              WHEN 'Net' THEN ZN(SUM([Final Call Off]))-ZN(SUM([Backlog Quantity]))

              WHEN 'Absolute' THEN ABS(ZN(SUM([Final Call Off])))-ABS(ZN(SUM([Backlog Quantity])))

          END

          }

           

          That shoudl fix the issue.  You are calculating the sum of the combined subtracted calculation before calling the abs() command.  by separating the calculation into two parts, the desired result of 21,400 appears.

           

          I hope that helps.

           

          Patrick