FABS() with a SUM()

[Anonymous]

Dear All,

I have the follow data structue:

dtSales, Value

dd/mm/yyyy, decimal value

In the value I have positive and negative values, I need an absolute sum per month/year.

I´m doing it with FABS(SUM(value)) in the expression and  date(dtSales,'MM/YYYY') in the dimension.

The problem is that when I show the total of vaue column it does not consider the fabs() function and show a total smaller then the expectation.

----

Attached is a file with the problem. Note that the first column is using the Fabs function and the second is just the sum and both have the same total in the top.

The correct SUM of Fabs fuction expected is 276,62 (see it in excel)

---

Any help? thanks in advance !

[Anonymous]

FABS(x) should return the absolute value of x. The result is a positive number.

I tested this and it works like I should expcat.

Can you share with us your Qlikview document so we can take a look?

[Anonymous]

as per my understanding..............u need only postive vaules even though they are -ve

try adding a column say fabs(units) as newunits then sum(newunits)

it will give your expected result

[Anonymous]

Done in original post. Thanks

[Anonymous]

srikanthsri1,

The issue is that I need the Fabs from the SUM of the MM/YYY and not from each value of the days.

[giakoum]

I think you are doing it in the wrong order :

this is how it should be : sum(FABS([RevTrack_Receita Vlr Cancelamento Bruto]))

giakoum

[Anonymous]

giakoum,

No, the line value is correct, the problem is the Subtotal calculated by the graphic.

I need the absolute value of the SUM of all values in each month/year. See that as you suggested, the value goes tmuch higher the expected 276,62

[giakoum]

Sorry misunderstood.

change it to straight table and then in the expression total mode select sum of rows.

See attached.

BR, giakoum

[Anonymous]

giakoum,

Excelent, worked fine. Can I have a function to do the same in a text box with the total value only?

I mean: I want a text box with the total value from this column. Can I use any aggregatin function?

Thanks in advance.

[giakoum · Accepted solution]

=num(sum(aggr(

          FABS(SUM([RevTrack_Receita Vlr Cancelamento Bruto])) /1000000

          , [RevTrack_Receita DtRef])), 'R$ #.##0,00;-R$ #.##0,00')