QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Rules, plus terms and conditions, can be found here.
Contributor

Calculation of percentage not working as expected

Hello everyone.  I am trying to perform a calculation but the result I am getting is not correct.  Here is a break down of the formula:

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Current Month])
Current Month = 132  this is correct

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month])
Prior Month = 142  this is correct

So what I would expect from the next formula is basically 132 - 142 / 142 which would be -0.0704...   or 7%

Instead I am getting 13000 percent.  Please will someone tell me what is wrong with my formula below?  Thank you

=num(Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Current Month])-
Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month])/
Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month]),'#0%')

1 Solution

Accepted Solutions
MVP

Re: Calculation of percentage not working as expected

Or this:

=Num(

(Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Current Month])-

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month]))/

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month]),'#0%')

Update: Its is simple bodmass rule problem

Order of Operations - BODMAS

6 Replies
MVP

Re: Calculation of percentage not working as expected

Try this:

=Num(

(Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Current Month])/

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month])) - 1

,'#0.0%')

MVP

Re: Calculation of percentage not working as expected

Or this:

=Num(

(Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Current Month])-

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month]))/

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month]),'#0%')

Update: Its is simple bodmass rule problem

Order of Operations - BODMAS

MVP

Re: Calculation of percentage not working as expected

Go to chart properties -> Number tab, check Number format is expression default.

Contributor

Re: Calculation of percentage not working as expected

Hi,

try this

=num(( Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Current Month])-

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month]) )/

Sum({<grp_typ = {'1. GROUP A','2. GROUP B','3. GROUP C'}>}[Prior Month]),'#0%')

Not applicable

Re: Calculation of percentage not working as expected

Hi Robert,

I can't check it directly (is better that you share a qv file to reply with example. Can you add the numerator between round brackets? So the part

 Sum({}[Current Month])- Sum({}[Prior Month])

is enclosed and calculated before the division.

Like this:

 =num((Sum({}[Current Month])- Sum({}[Prior Month]))/ Sum({}[Prior Month]),'#0%')
Contributor

Re: Calculation of percentage not working as expected

Thank you I had a feeling it was simply the order of operations but I wasn't putting the parenthesis in the right place.  kushal your help is much appreciated as well