Wrong Result Row % Calculation

[Former Member]

Note: Use ! as column seperator.

K1 ! K2 ! D=K1-K2! D%A K1 ! D1=ABS(K1-K2)! D1%A K1

-

---

1,020 ! 750 ! 270 ! 26 % 270 ! 26 %

340 !1900 ! -1,560! -459% 1,560 ! 459 %

34,201! 34,201! 100 % 34,201 ! 100 %

5,551 ! 5,551 ! 100 % 5,551 ! 100 %

1,160 ! 120 ! 1,040 ! 90 % 1,040 ! 90 %

-

---

42,272! 2,770 ! 39,502! 93 % ! 42,622 ! 93%

ERROR column is D1%A K1.

Result row 93% is supposed to be 101%. Appears like its using 39502 of column D=K1-K2 instead of 42622 of column D1=ABS(K1-K2) and doing the calculation.

Tried all the formula collision options. Fun part is there is one more formula field in the report with same formula as (k1-k2)%A k1 or D %A k1and i get right result here. But it doesnt give me the right result for other formula field with same calculation except that it is ABS(K1-K2)%A K1 or D1 %A K1.

Thanks

Kiran.

[Former Member]

Hi Kiran

Would you check again the values you have posted above.

look at 3 rd line

K1 = 34201 , K2=34201 and then D = 0

D %A K1 = 0 % 34201 will be 0 and not 100%.

Please check it again.

[Former Member]

Hi Pradip,

We are missing a 0 in 3rd and 4th rows for second column. I guess thats the cause for confusion.

Anyway what's happening exactly here is:

Though i force the result row to display as summation for the absolute difference of all the rows for D1, when its doing the calculation its taking the absolute difference of sum of k1- sum of k2 at result row level.

k1 ! k2 ! k1-k2 ! abs(k1-k2)

1,020 ! 8,105 ! -7085 ! 7085

34,201! 29,665 ! 4536 ! 4536

5,551 ! 4,250 ! 1301 ! 1301

1,160 ! 10,372 ! -9212 ! 9212

-

---

41932 52392 -10460 22134

I guess this will make it clear for you now. Instead of using 22134 which is sum of absolute differences it is using absolute of (41932-52392) = 10460 at the calculation time.

What we end up with Result row calculation: instead of 22134 %A 41932 it is 10460 %A 41932.

I guess thats how OLAP works.

Kiran.