topic Fabs function giving wrong total in QlikView

Fabs function giving wrong total

arulsettu — Tue, 26 May 2015 09:34:48 GMT

hi i am trying to show negative values to positive

so did like this

=fabs(sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1))

but my sub total is wrong any one tell me why?

Thanks

Re: Fabs function giving wrong total

maxgro — Tue, 26 May 2015 09:38:09 GMT

where do you want the fabs?

fabs(sum....)

sum( ....fabs(TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1))

Re: Fabs function giving wrong total

awhitfield — Tue, 26 May 2015 09:39:13 GMT

HI Arul,

it should be =sum(fabs({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1))

Fabs and sum are the wrong way round!

HTH Andy

Re: Fabs function giving wrong total

tresB — Tue, 26 May 2015 09:41:25 GMT

Try like:

=sum({<ACNT_FLEX_02={'Balance Sheet'}>} Fabs(TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1))

Re: Fabs function giving wrong total

prajapatiamar38 — Tue, 26 May 2015 09:42:57 GMT

Hiii..

Try like this

=sum(fabs({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1))

I hope it will work

Thanks

Re: Fabs function giving wrong total

Tue, 26 May 2015 09:44:36 GMT

I would try =sum(fabs({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1))

Re: Fabs function giving wrong total

arulsettu — Tue, 26 May 2015 09:46:55 GMT

it showing error

=sum(fabs({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1))

Re: Fabs function giving wrong total

arulsettu — Tue, 26 May 2015 09:48:53 GMT

it showing correct total. but the value changed

Re: Fabs function giving wrong total

Colin-Albert — Tue, 26 May 2015 09:48:54 GMT

Your expression is calculating the sum of the column and then using fabs() to show the absolute value after summing the records.

=fabs(sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1))

If you want to calculate the absolute value of each record before summing the result, then you need to include

the fabs() function inside the sum.

=sum({<ACNT_FLEX_02={'Balance Sheet'}>} fabs(TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1) )

Re: Fabs function giving wrong total

tresB — Tue, 26 May 2015 09:49:07 GMT

Fabs() is not an aggregation function, hence it shows error with set analysis expression in it. Try the way I showed above.

Re: Fabs function giving wrong total

arulsettu — Tue, 26 May 2015 09:51:07 GMT

Colin Sum(fabs(...

gives error

Re: Fabs function giving wrong total

Colin-Albert — Tue, 26 May 2015 09:54:08 GMT

I updated the expression to
sum({<ACNT_FLEX_02={'Balance Sheet'}>} fabs(TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1) )

Re: Fabs function giving wrong total

arulsettu — Tue, 26 May 2015 09:54:36 GMT

Hi Tresesco,

yours works fine. total is correct but shows incorrect value why?

Re: Fabs function giving wrong total

tresB — Tue, 26 May 2015 09:54:47 GMT

Yes, that would change the value, if you want the absolute value of the sum, try like:

Sum(Aggr(Fabs(sum({<ACNT_FLEX_02={'Balance Sheet'}>} TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)), Dimension1, dimension2...))

Re: Fabs function giving wrong total

Colin-Albert — Tue, 26 May 2015 09:58:37 GMT

I just notice the minus sign between the two fields, Adding spaces makes the expressions easier to read,

Try this

fabs(

sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1)

sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_CR_LC_1)

)

fabs( sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1))

fabs( sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_CR_LC_1))

Re: Fabs function giving wrong total

arulsettu — Tue, 26 May 2015 10:01:30 GMT

Incorrect subtotal colin

Re: Fabs function giving wrong total

Colin-Albert — Tue, 26 May 2015 10:03:46 GMT

Can you post some example data and the subtotal you are expecting to see?

Re: Fabs function giving wrong total

tresB — Tue, 26 May 2015 10:14:32 GMT

Did you check the 2nd expression i suggested above?

Re: Fabs function giving wrong total

arulsettu — Tue, 26 May 2015 10:17:29 GMT

this one

Sum(Aggr(Fabs(sum({<ACNT_FLEX_02={'Balance Sheet'}>} TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)), Dimension1, dimension2...))

i replaced two dimesions in my chart above expression but incorrect value

Re: Fabs function giving wrong total

arulsettu — Tue, 26 May 2015 10:25:37 GMT

plz some one check my sample app