In Pivot table, How to fabs firstly then sum

[#1]

Hi, Guys

I met a big problem on ABS function

I need to get the final result is sum all the item absloute deviation

for example

item	Forecast	Demand
a	10	12
b	10	8
c	10	10

The correct anwser is |10-12|+|10-8|+|10-10| = 4

not |10+10+10 - 12-8-10| = 0

From my previous experience, I create a temp table to store each absloute deviation, the put them in the pivot table

However, the new case is the Demand is composed of three kinds of Demand

Take first row 12 as example, 12 comes from  8(current)+4 (future) + 0 (old)

The user want to add a filter to customize the real Demand (8+4 or 8 lonely)

Then how do I create the  temp table to calculate each independent absloute deviation to suit for the filter?

[jagan]

Hi,

Just give month as dimension.  The expression remains same.

Regards,

Jagan.

[jagan]

Hi,

Just give month as dimension.  The expression remains same.

This will work.

Regards,

Jagan.

[#4]

Hi,  thanks for your suggestions

And now there is a new questions after three month

Range Sum is the bottom level sum depends on the data

in my raw data, the bottom level is Month & Product

Quarter	Month	Product	Forecast	Demand
2015Q1	201501	P1	100	50
2015Q1	201503	P2	20	50
2015Q1	201503	P2	80	100

when I use your formula  Sum(Fabs(RangeSum(Forecast, -Demand))) 

It could only get the monthly abs

How can I get the quarterly abs , and is it possible to have a formula or table to get each quarterly abs then dividied into monthly abs? (Use aggr or sum total)

[sunny_talwar]

May be this:

Sum(Aggr(Fabs(RangeSum(Forecast, -Demand)), Product, Month))