29 Replies Latest reply: Sep 13, 2017 3:50 AM by Akshada Aniruddha Parkar

Fabs total issue

Hi Experts - in the attached qv file.. I have created an expression which says Column B value Column A values... By the use of "Fabs" function i got all the expression values in positive.. But now i need the Total of all the values which came from my fab function..

I.e., in the attached example .. Total of my Expression should be 200 where as it shows only 40.. Please Help!

• Re: Fabs total issue

May be this?

Sum(Aggr(Fabs(sum(B)-Sum(A)), A, B))

• Re: Fabs total issue

Hi Anil.. I used your Formulae... But i see some wrong calculation happening here.. Chk the highlighted one..

• Re: Fabs total issue

What expression you used in real data? I assume, you have only one Dimension so with in the aggregation you can use Only one dimension

• Re: Fabs total issue

Yes.. I have only 1 Dimension and 3 Expression..

• Re: Fabs total issue

Can you share updated work file

• Re: Fabs total issue

Sorry... in Agg function.. i didnt use Dimension.. i used my Expression .. Now working fine

• Re: Fabs total issue

Good

• Re: Fabs total issue

Hi Anil...  When i click on particular month.. i see right number.. but if more than a month is selected.. number seem to be wrong..

any idea ?

• Re: Fabs total issue

May be you can try this?

Only({<Month>} Sum(Aggr(Fabs(sum({<Month>}B)-Sum({<Month>}A)), A, B)))

OR

If(GetFieldSelections(Month) >=1,Sum(Aggr(Fabs(sum(B)-Sum(A)), A, B)), Fabs(Sum(B)-Sum(A)))

• Re: Fabs total issue

Not working

• Re: Fabs total issue

Only({<Month>} Sum(Aggr(Fabs(sum({<Month>}B)-Sum({<Month>}A)), A, B)))

Above one is showin as wrong expression

• Re: Fabs total issue

Try this one? Or else please share sample

Sum({<Month>} Aggr(Fabs(sum({<Month>}B)-Sum({<Month>}A)), A, B))

• Re: Fabs total issue

Hi Anil - I have added month to the above data... Now i see the total as 80..instead of 240.. Can u plz chk this?

• Re: Fabs total issue

Do you mean this one is wrong?

• Re: Fabs total issue

Actually.. if u take Jan month and Deduct on the line item level... We see total as 60 instead of 20.. That is the issue i face now..

• Re: Fabs total issue

From above screen shot, Can you make it clear how you are Deduct the Jan month?

• Re: Fabs total issue

For Jan we have 3 line items.. refer to my excel screen shot in above reply... each line item.. ABS Subtract is 20 each... so it should be 60..

• Re: Fabs total issue

Like this?

• Re: Fabs total issue

try below expression, should work

sum(aggr(fabs(sum(B)-Sum(A)),Month,A,B))

• Re: Fabs total issue

expression  tab, Total Mode --> Sum of Rows

• Re: Fabs total issue

This option is not available in pivot table

• Re: Fabs total issue

i used his sample. where is he mentioning using pivot?

• Re: Fabs total issue

What if Pivot?

• Re: Fabs total issue

thats not the question.

in his example he is using straight table

• Re: Fabs total issue

TY!..I need the number to be appearing in Text box as well..

• Re: Fabs total issue

=sum(aggr(fabs(sum(B)-Sum(A)),A,B))

• Re: Fabs total issue

Hi Bharani,

Try this,

Sum(fabs(B-A))

Hope this helps you!

Regards,

Pratik

• Re: Fabs total issue

Hi Bharani