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

[linusblomberg]

How much is A approximately?

and how much is B approximately?

[arulsettu]

A=-190,144,683

B=-338,231,725

[Peter_Cammaert]

Like Linus suggests, put the parts of the big expression as separate expressions in a text box. For example:

='A = ' & (ExpressionA) & chr(10) &

'B = ' & (ExpressionB)

Also note that when applying num() with a format string that includes a %-symbol requires an expression result between 0 and 1 to get 0-100%. The expression should be (A-B)/A instead of (A-B)/A*100.

Also check the format string for correct decimal and thousand separators. It looks like QV is messing up the output due to separator misinterpretations.

Best,

Peter

[linusblomberg]

Ok good. The negative values make it a little bit more difficult to follow along but what we see is that

-190/-338 = 0.56. A is 56 percent of B.

the difference of B-A = -148 in percent /-338=0.44

[arulsettu]

like this peter

=num(sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1) & Chr(10) -

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) & Chr(10) /

num(sum({<ACNT_FLEX_02={'Balance Sheet'}>}TBAL_MTD_DR_LC_1-TBAL_MTD_CR_LC_1)*100,'#,##0%'))

it gives me blank value

[arulsettu]

can u tell me so how do it?

thanks

[Peter_Cammaert]

Hmmm, maybe this sheds some light from another direction:

This text box expression:

='A = ' & num(-190144683, '#,##0') & chr(10) &

'B = ' & num(-338231725, '#,##0') & chr(10) &

'A-B = ' & num(-190144683+338231725, '#,##0') & chr(10) &

'% = ' & num((-190144683+338231725)/-190144683, '#,##0%')

with default separators

SET ThousandSep='.';

SET DecimalSep=',';

produces this result

[Peter_Cammaert]

Sorry, that's no what I meant. See below for an example.

BTW if you get a blank, then there is something wrong with the expression itself and not necessarily with the formatting.

[arulsettu]

thanks i will check and let u know

[arulsettu]

i did this

=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%')

result is 4,563,595,236,928%

i need to show like 45%

can plz tell me how to do it