Get values in percentage

[arulsettu]

Hi,

i used below code to get values in percentage

=num(sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)-sum({<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'0'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)+Sum({$<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-11)))<=$(=date(Addmonths(max(datefield),-7)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)/

sum({Total<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'0'}>}TOTAL TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)+Sum({Total$<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-11)))<=$(=date(Addmonths(max(datefield),-7)))"}>}TOTAL TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)*100

,'#,##0%')

but this what i get

106,080,500,56%

what i am doing wrong here i used num function

and Total also

plz suggest something

thanks

[Peter_Cammaert]

I think there are a couple of problems with your expression:

• Analysis reveals that your expression looks like: (A - B + C)/(B+C)*100. Is this correct? It's not compatible with your initial statement of (A-B)/A*100. Missing parentheses? Wrong order?
• You are still multiplying by 100 AND using the %-symbol in the format string. Your result is at least a factor 100 of target.

What are your default thousand and decimal separators?

Best,

Peter

[linusblomberg]

You need to go through your expressions. Can you write Sum({Total$<AC....? Do you mean Sum({1<AC....

[arulsettu]

the formula is (A-B)/B*100

SET ThousandSep=',';

SET DecimalSep='.';

thanks

[Peter_Cammaert]

Yes it should be, but it's not. Check it out:

=num(

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

-

sum({<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'0'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

+

Sum({$<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-11)))<=$(=date(Addmonths(max(datefield),-7)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1) )

/

sum({<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH={'0'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

+

Sum({$<ACNT_FLEX_02={'Balance Sheet'},TBAL_CAL_MONTH=,TBAL_CAL_YEAR=, datefield={">=$(=date(Addmonths(max(datefield),-11)))<=$(=date(Addmonths(max(datefield),-7)))"}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)

*

100

,'#,##0%')

It's even worse, because it looks more like this: (A - B + C) / B + C * 100 which due to operator precedence will produce completely different results (more like ((A-B+C)/B) + (C*100). Note: A=red, B=blue, C=green. No correspondence to your intended expression.

I didn't change any character, just the coloring and the whitespace.

Please fix this first before we go on trying to format the end result.

Peter

[Peter_Cammaert]

BTW using the num(Result, '#,##0%') format string and your default separators you want percentages to be displayed in the thousands, and without decimals, correct?

[arulsettu]

yes