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

# NET OR ABS value using LODs

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

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