Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
How much is A approximately?
and how much is B approximately?
A=-190,144,683
B=-338,231,725
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
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
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
can u tell me so how do it?
thanks
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
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.
thanks i will check and let u know
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