topic Re: Get values in percentage in QlikView

Get values in percentage

arulsettu — Thu, 26 Mar 2015 05:45:19 GMT

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

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 05:53:55 GMT

formula is

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

B= 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)

Percentage = (A+B)/B*100

Re: Get values in percentage

PrashantSangle — Thu, 26 Mar 2015 06:01:07 GMT

Hi,

In your expression either multiple by 100 or just use

NUM((A+B)/B,'#,##0%')

Regards

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 06:04:42 GMT

Hi max,

plz check first code i multiplied by 100. and i need to show this in a text object

Re: Get values in percentage

PrashantSangle — Thu, 26 Mar 2015 06:13:23 GMT

Hi,

In first expression you are multiplying by 100 as well as formating the output with num(Expre,'#,##0%')

Try below

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)/

,'#,##0%')

,'#,##0')&' %'

Regards

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 06:36:52 GMT

Hi max,

No luck only blank value

thanks

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 07:08:56 GMT

any suggestion guys?

Re: Get values in percentage

linusblomberg — Thu, 26 Mar 2015 07:31:39 GMT

You lost me, what do you want to achieve?

Are you sure you don't want (B-A)/B? Percentage is basically Part/Whole not (Part+Whole)/Whole.

Give the number for A and B instead of the expression and try it.

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 07:44:27 GMT

sorry (A-B)/A*100

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 08:00:16 GMT

i did this (A-B)/A*100

but the result is like this 106,080,500,56%

why?

thanks

Re: Get values in percentage

linusblomberg — Thu, 26 Mar 2015 08:03:09 GMT

How much is A approximately?

and how much is B approximately?

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 08:10:33 GMT

A=-190,144,683

B=-338,231,725

Re: Get values in percentage

Peter_Cammaert — Thu, 26 Mar 2015 08:19:21 GMT

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

Re: Get values in percentage

linusblomberg — Thu, 26 Mar 2015 08:21:31 GMT

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

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 08:28:20 GMT

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

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 08:29:06 GMT

can u tell me so how do it?

thanks

Re: Get values in percentage

Peter_Cammaert — Thu, 26 Mar 2015 08:32:04 GMT

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

Re: Get values in percentage

Peter_Cammaert — Thu, 26 Mar 2015 08:34:05 GMT

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.

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 08:38:30 GMT

thanks i will check and let u know

Re: Get values in percentage

arulsettu — Thu, 26 Mar 2015 08:54:54 GMT

i did this

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

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

i need to show like 45%

can plz tell me how to do it