Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
thnkgreen
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

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

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%')

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

Re: Calculation of percentage not working as expected

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

narendiran
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({<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])

is enclosed and calculated before the division.

Like 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%')

thnkgreen
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

Community Browser