topic Re: In Pivot table, How to fabs firstly then sum in QlikView

In Pivot table, How to fabs firstly then sum

Wed, 04 Nov 2015 11:15:33 GMT

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?

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

Anonymous — Wed, 04 Nov 2015 12:06:06 GMT

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

sunny_talwar — Wed, 04 Nov 2015 12:06:17 GMT

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

Thu, 05 Nov 2015 02:46:52 GMT

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

marcohadiyanto — Thu, 05 Nov 2015 03:30:20 GMT

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

jagan — Thu, 05 Nov 2015 03:56:31 GMT

Hi,

You can try using Aggr() like below

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

OR the best thing is calculate in the script itself

LOAD

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

PrashantSangle — Thu, 05 Nov 2015 03:58:30 GMT

Hi,

did you try simple expression like

sum(fabs(Forecast-Demand))

This will work.

Regards

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

Thu, 05 Nov 2015 06:57:37 GMT

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

Thu, 05 Nov 2015 06:59:55 GMT

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

marcohadiyanto — Thu, 05 Nov 2015 11:29:34 GMT

Hi Jarven,,

Can you share your simple data and your expected result so we can help you.

Thanks

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

jagan — Thu, 05 Nov 2015 11:35:16 GMT

Hi,

Just give month as dimension. The expression remains same.

Regards,

Jagan.

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

jagan — Fri, 06 Nov 2015 02:06:26 GMT

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

Wed, 20 Jan 2016 08:51:30 GMT

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)

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

sunny_talwar — Wed, 20 Jan 2016 10:01:19 GMT

May be this:

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