topic Re: Percentage calculation using variables in QlikView

Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 15:25:11 GMT

I have the following two variables with the values tested in text boxes:

I am trying to get the % change from current versus the previous as follows:

=$(vSetCurrentAvRevRx)/$(vSetPreviousAvRevRx) - 1 and formatted as a percentage in number tab option

which should give -1.56% but i am getting a different value (-.99999). My expression above should be the problem. Kindly assist.

Regards

Message was edited by: Christopher Chitemerere

Re: Percentage calculation using variables

Mark_Little — Mon, 23 Nov 2015 15:27:10 GMT

Hi,

Try this

=NUM($(vSetCurrentAvRevRx)/$(vSetPreviousAvRevRx) *- 1,'#,##0.00')

Mark

Re: Percentage calculation using variables

lironbaram — Mon, 23 Nov 2015 15:31:09 GMT

if your variable are holding numbers i.e they variable expression starts with '=' sign

you might want to try num(vSetCurrentAvRevRx/vSetPreviousAvRevRx - 1 ,'##0.00%')

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 15:35:08 GMT

Thanks Mark for your contribution. The expression gives a blank result.

Re: Percentage calculation using variables

Mark_Little — Mon, 23 Nov 2015 15:37:41 GMT

HI,

Could be like Liron has suggested, try the below.

Mark

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 15:42:41 GMT

Thanks Liron

Your suggestion gives a blank. The expressions are as follows:

Current

Num((Sum({$<$(vSetMTD)>}[MedAid Amount])) + Num(Sum({$<$(vSetMTD)>}[Shortfall])), '$##,##0.00')/Num(Sum({$<$(vSetMTD)>}Rx), '#,##0')

Num((Sum({$<$(vSetPreviousMonth)>}[MedAid Amount])) + Num(Sum({$<$(vSetPreviousMonth)>}[Shortfall])), '$##,##0.00')/Num(Sum({$<$(vSetPreviousMonth)>}Rx), '#,##0')

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 15:43:36 GMT

hi,

try this:

=num(($(vSetCurrentAvRevRx)/$(vSetPreviousAvRevRx))-1,'# ##0,00 %')

With your expressions, it should be:

Num(

((Sum({$<$(vSetMTD)>}[MedAid Amount]) +Sum({$<$(vSetMTD)>}[Shortfall]))/Sum({$<$(vSetMTD)>}Rx))

((Sum({$<$(vSetPreviousMonth)>}[MedAid Amount]) + Sum({$<$(vSetPreviousMonth)>}[Shortfall]))/Sum({$<$(vSetPreviousMonth)>}Rx)),

'# ##0,00 %')

Or maybe '# ##0.00 %' it depends on your number format

Re: Percentage calculation using variables

tamilarasu — Mon, 23 Nov 2015 15:46:27 GMT

Hi Christopher,

Try like,

=($(vSetCurrentAvRevRx)/$(vSetPreviousAvRevRx) )- 1

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 15:48:17 GMT

Many thanks, this does not seem to work

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 15:52:13 GMT

Thanks Tamil, i am getting a blank with your suggestions.

Re: Percentage calculation using variables

tamilarasu — Mon, 23 Nov 2015 15:54:46 GMT

Chirstopher, Add equal sign like below.

=($(=vSetCurrentAvRevRx)/$(=vSetPreviousAvRevRx) )- 1

Or you can prefix equal sign (=) before num syntax in your both current and previous expressions and try below

=($(vSetCurrentAvRevRx)/$(vSetPreviousAvRevRx) )- 1

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 16:02:46 GMT

are your sure that your variables are correct.

Test them in case of

Re: Percentage calculation using variables

Mon, 23 Nov 2015 16:07:49 GMT

Hi Christopher

here a qvw

best regards

chrisitan

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 16:15:13 GMT

Thanks Tamil, i get an error: Error in expression

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 16:16:16 GMT

The values returned by the variables are give in the textboxes posted in my initial post.

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 16:19:35 GMT

Many thanks, you are on the right track you missed the -1 and should be as follows:

Num(

((Sum({$<$(vSetMTD)>}[MedAid Amount]) +Sum({$<$(vSetMTD)>}[Shortfall]))/Sum({$<$(vSetMTD)>}Rx))

((Sum({$<$(vSetPreviousMonth)>}[MedAid Amount]) + Sum({$<$(vSetPreviousMonth)>}[Shortfall]))/Sum({$<$(vSetPreviousMonth)>}Rx)) - 1,

'# ##0,00 %')

This give the correct value as posted in my initial post. I am however looking as using the variables.

Re: Percentage calculation using variables

tamilarasu — Mon, 23 Nov 2015 16:28:00 GMT

Ok.Did you try the second solution. Go to variable window and add equal sign in your expressions like below

Current

=Num((Sum({$<$(vSetMTD)>}[MedAid Amount])) + Num(Sum({$<$(vSetMTD)>}[Shortfall])), '$##,##0.00')/Num(Sum({$<$(vSetMTD)>}Rx), '#,##0')

=Num((Sum({$<$(vSetPreviousMonth)>}[MedAid Amount])) + Num(Sum({$<$(vSetPreviousMonth)>}[Shortfall])), '$##,##0.00')/Num(Sum({$<$(vSetPreviousMonth)>}Rx), '#,##0')

And try

=($(vSetCurrentAvRevRx)/$(vSetPreviousAvRevRx) )- 1

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 17:20:43 GMT

Thank you Christian, i will look at the qvw

Regards.

Re: Percentage calculation using variables

Anonymous — Mon, 23 Nov 2015 18:42:42 GMT

Hi Tamil

Tried the second option above and gives a blank