topic Re: Absolute Value Totals in QlikView

Absolute Value Totals

Tue, 30 Jul 2013 16:10:17 GMT

The total is adding the absolute values together vertically in the straight table, but I need the absolute values to calculate horizontally.

My Table:

Forecast Shipments Absolute Value

0 10 10

14 5 9

14 15 19 <-- TOTAL ROW

The absolute value in the total row should not be added together (19), it should be (1) which is the absolute value calculation of (14) and (15). the total mode in the straight table is set to "Expression Total"

At the line level, things seem to be calculating correctly until you look at the Total Row.

Any help would be much appreciated!

Thanks,

Parrish

Re: Absolute Value Totals

Tue, 30 Jul 2013 16:16:11 GMT

I'm assuming that Absolute Value is actually a column in your dataset, this being the case it will always give you the above answer.

Try defining Absolute value as Forecast - Shipments, if you do that then the total row should show you the value your looking for as long as you also have the total tye set to "Expression Total" and NOT "Sum of Rows".

Re: Absolute Value Totals

Tue, 30 Jul 2013 16:25:15 GMT

The Abolute Value is actually not a column in the dataset, it is being created using an expression which is based off of the forecast and the shipments:

the expression being used is as follows:

sum(AGGR(sum(FABS(Forecast - Shipments)), Key))

Re: Absolute Value Totals

Tue, 30 Jul 2013 16:40:48 GMT

OK, and why are you aggregating that expression by Key? Is this not a dimension in your table?

Re: Absolute Value Totals

Tue, 30 Jul 2013 16:42:15 GMT

Why does a simple FABS(Sum(Forecast-Shipments)) not work for you?

Re: Absolute Value Totals

Anonymous — Wed, 27 Nov 2013 15:54:07 GMT

Hi Parrish

To calculate Horizontally this data, simply use:

=sum(fabs(forecast-shipment))

And.. in QVW, Property of graphic, expression tab, set [total mode] = [without total]

See the expected result:

See the attached file with solution:

Regards

Evandro