topic Re: FABS() with a SUM() in QlikView

FABS() with a SUM()

Anonymous — Tue, 19 Jun 2012 12:51:58 GMT

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 !

Re: FABS() with a SUM()

Anonymous — Tue, 19 Jun 2012 13:27:56 GMT

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?

Re: FABS() with a SUM()

Anonymous — Tue, 19 Jun 2012 13:41:18 GMT

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

Re: FABS() with a SUM()

Anonymous — Tue, 19 Jun 2012 13:52:19 GMT

Done in original post. Thanks

Re: FABS() with a SUM()

Anonymous — Tue, 19 Jun 2012 13:55:20 GMT

srikanthsri1,

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

Re: FABS() with a SUM()

giakoum — Tue, 19 Jun 2012 14:17:14 GMT

I think you are doing it in the wrong order :

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

giakoum

Re: FABS() with a SUM()

Anonymous — Tue, 19 Jun 2012 14:31:06 GMT

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

Re: FABS() with a SUM()

giakoum — Tue, 19 Jun 2012 14:42:14 GMT

Sorry misunderstood.

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

See attached.

BR, giakoum

Re: FABS() with a SUM()

Anonymous — Tue, 19 Jun 2012 14:52:05 GMT

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.

Re: FABS() with a SUM()

giakoum — Tue, 19 Jun 2012 15:09:25 GMT

=num(sum(aggr(

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

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

Re: FABS() with a SUM()

sebastiandperei — Tue, 19 Jun 2012 15:15:06 GMT

Hi alvrorogrigues,

(si sabés español, como supongo, avisame que para mí es mejor!)

The problem is that three periods (12/09, 05/09 and 03/09) has different sign that the rest.Whitout FABS, all are negative but these three.

In each period, Sum takes the sum of all values from the period, and FABS if its negative, converts to positive. In total, Sum takes (again) the summ of all values from all the data, and then if the result (not for sum of each period like you are doing with excel) is negative, convert to positive.

Depends on what you need. If you need to get the sum of each period value, you have two choices:

- use the folowing expression:

=sum(AGGR(FABS(SUM([RevTrack_Receita Vlr Cancelamento Bruto])), [RevTrack_Receita DtRef]))

/1000000

- use a pivot table instead of straight table, and select "Sum of Files" (instead Total expression) in Total Mode.

If you need that the TOTAL works like same way as the rest of values (first SUM, then get absolute value), the applications is working good.

Re: FABS() with a SUM()

erichshiino — Tue, 19 Jun 2012 15:20:15 GMT

Hi, I guess I also answered your post in QlikView Brasil...

But now that I saw your original .qvw

You can use the expressions suggested by giakoum but you can leave the number formatting to the number properties tab of the object.

sum ( aggr(

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

, [RevTrack_Receita DtRef] ) )

Regards,

Erich

Re: FABS() with a SUM()

Anonymous — Tue, 19 Jun 2012 15:39:12 GMT

Thanks all.

Re: FABS() with a SUM()

sebastiandperei — Tue, 19 Jun 2012 17:12:26 GMT

Hehe... Espero al menos te sirva mi explicación Alvaro!!

Saludos,