13 Replies Latest reply: Jan 20, 2016 5:01 AM by Sunny Talwar

# In Pivot table, How to fabs firstly then sum

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

itemForecastDemand
a1012
b108
c1010

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?

• ###### Re: In Pivot table, How to fabs firstly then sum

if Forecast and Demand are two expression in your chart, then use 3rd expression like:

Forecast - Demand  //Expression label name, you can use abs() as well

• ###### Re: In Pivot table, How to fabs firstly then sum

in item level it worked

but if in higher level, the summary will sum first then abs

• ###### Re: In Pivot table, How to fabs firstly then sum

May be something like this;

Sum(Fabs(RangeSum(Forecast, Demand))) or

Sum(Aggr(Fabs(RangeSum(Forecast, Demand)), Item))

Sum(Fabs(RangeSum(Forecast, -Demand))) or

Sum(Aggr(Fabs(RangeSum(Forecast, -Demand)), Item))

• ###### Re: In Pivot table, How to fabs firstly then sum

Thanks for your guys anwser, the total amount will be aggrated on the top by the straight table

But in addition, if i want to display in month level ,for example

201505     2

201506     4

In pivot table it may expand to monthly item level, but here we knows must use straight table , so how to display the month level abs?

I need a chart to describe the monthly trend, so i need each month point abs first then sum chooco_co

• ###### Re: In Pivot table, How to fabs firstly then sum

Hi,

Just give month as dimension.  The expression remains same.

This will work.

Regards,

Jagan.

• ###### Re: In Pivot table, How to fabs firstly then sum

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

QuarterMonthProductForecastDemand
2015Q1201501P110050
2015Q1201503P22050
2015Q1201503P280100

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)

• ###### Re: In Pivot table, How to fabs firstly then sum

May be this:

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

• ###### Re: In Pivot table, How to fabs firstly then sum

Hi,

You can use this as your expression

sum(aggr(fabs(Forecast-Demand),Item))

Hope this help

Regards,

Marco

• ###### Re: In Pivot table, How to fabs firstly then sum

Hi,

You can try using Aggr() like below

=Sum(Aggr(Fabs(Forecast-Demand), item))

OR the best thing is calculate in the script itself

*,

Forecast - Demand AS ForecastDiff

FROM DataSource;

Now you can straight away use

=Sum(ForecastDiff)

Note: If data is huge Aggr() will have performance issues.

Hope this helps you.

Regards,

Jagan.

• ###### Re: In Pivot table, How to fabs firstly then sum

Thanks for your guys anwser, the total amount will be aggrated on the top by the straight table

But in addition, if i want to display in month level ,for example

201505     2

201506     4

In pivot table it may expand to monthly item level, but here we knows must use straight table , so how to display the month level abs?

I need a chart to describe the monthly trend, so i need each month point abs first then sum

• ###### Re: In Pivot table, How to fabs firstly then sum

Hi Jarven,,

Thanks

• ###### Re: In Pivot table, How to fabs firstly then sum

Hi,

Just give month as dimension.  The expression remains same.

Regards,

Jagan.

• ###### Re: In Pivot table, How to fabs firstly then sum

Hi,

did you try simple expression like

sum(fabs(Forecast-Demand))

This will work.

Regards