Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
LINKEDIN LIVE: Democratizing data to enhance customer-centricity. JULY 29TH REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
arulsettu
Master III
Master III

Get values in percentage

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

25 Replies
linusblomberg
Creator II
Creator II

How much is A approximately?

and how much is B approximately?

arulsettu
Master III
Master III
Author

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
Creator II
Creator II

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
Master III
Master III
Author

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
Master III
Master III
Author

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

PercentageFormaatingProblem thread157673.jpg

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
Master III
Master III
Author

thanks i will check and let u know

arulsettu
Master III
Master III
Author

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