# NET OR ABS value using LODs

**Ganesh Vijay Kumar**Aug 20, 2018 12:32 PM

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