January 27, 2016 at 11:47 am
I am trying to apply the following expression to a report, but keep receiving the wrong result. The formula is: (postMisc1-preMisc1)/(postMisc1*100). In my case it should be using the following numbers: (419-1174)/(1174*100). The result should be -64.3100.... Instead I am getting -0.0180.... Here is the expression I have put into Report Builder:
=((SUM(IIF(Fields!postMisc1.Value,1,0))-SUM(IIF(Fields!preMisc1.Value,1,0)))/(SUM(IIF(Fields!postMisc1.Value,1,0))*100))
The fields that are being Summed are Boolean values.
If I calculate both sides of the / individually, they give the correct result, so I'm guessing it is being divided incorrectly somehow.
Does anyone know what I am doing wrong with the expression?
January 27, 2016 at 11:55 am
First thing I notice is that your expression doesn't match what you stated in the text. In the text you stated you want to divide by preMisc1*100, but in the expression you have postMisc1 in the denominator.
Cheers!
January 27, 2016 at 11:58 am
Jacob Wilkins (1/27/2016)
First thing I notice is that your expression doesn't match what you stated in the text. In the text you stated you want to divide by preMisc1*100, but in the expression you have postMisc1 in the denominator.Cheers!
Thank you. I didn't catch that. It is still giving the wrong result, but at least close to the correct numbers. Here is the updated expression:
=((SUM(IIF(Fields!postMisc1.Value,1,0))-SUM(IIF(Fields!preMisc1.Value,1,0)))/(SUM(IIF(Fields!preMisc1.Value,1,0))*100))
The new result is -0.006431005....
January 27, 2016 at 12:04 pm
bsmith 63193 (1/27/2016)
Jacob Wilkins (1/27/2016)
First thing I notice is that your expression doesn't match what you stated in the text. In the text you stated you want to divide by preMisc1*100, but in the expression you have postMisc1 in the denominator.Cheers!
Thank you. I corrected the post to display the correct expression.
Still think there's some confusion.
If post=419 and pre=1174, then you're getting the correct result of the (post-pre)/(post*100) expression.
((419-1174)/(419*100))=-.018
Your example with the new numbers is doing (post-pre)/(pre*100), which incidentally is not the number you say you want.
((419-1174)/(1174*100))=-0.006431005110
I think we just need to get very clear on exactly what you need to be doing, as the requirements are inconsistent. The value you want would be achieved with a different expression than you say you want.
Just need to get the requirements clear, and then it'll be easy 🙂
January 27, 2016 at 12:11 pm
Jacob, you beat me in fixing my correction. I am now getting the same result as you with the -0.006431... When I type 419-1174/1174*100 into a calculator I get the expected result of -64.3100...
January 27, 2016 at 12:11 pm
bsmith 63193 (1/27/2016)
Jacob Wilkins (1/27/2016)
First thing I notice is that your expression doesn't match what you stated in the text. In the text you stated you want to divide by preMisc1*100, but in the expression you have postMisc1 in the denominator.Cheers!
Thank you. I didn't catch that. It is still giving the wrong result, but at least close to the correct numbers. Here is the updated expression:
=((SUM(IIF(Fields!postMisc1.Value,1,0))-SUM(IIF(Fields!preMisc1.Value,1,0)))/(SUM(IIF(Fields!preMisc1.Value,1,0))*100))The new result is -0.006431005....
So, which takes precedence, the desired expression or the desired result? If you're just looking for an expression that gets you the desired result, then just move the *100 so it's the outermost operation, applied to the result of the division.
Cheers!
EDIT: The reason you get it with a calculator is likely because you do this: (post-pre), then /pre, then *100. That's equivalent to this: ((post-pre)/pre)*100. It's all about the scope of the *100.
It's just a question of exactly what the required calculation is. What question is this calculation trying to answer?
January 27, 2016 at 12:13 pm
Jacob Wilkins (1/27/2016)
bsmith 63193 (1/27/2016)
Jacob Wilkins (1/27/2016)
First thing I notice is that your expression doesn't match what you stated in the text. In the text you stated you want to divide by preMisc1*100, but in the expression you have postMisc1 in the denominator.Cheers!
Thank you. I didn't catch that. It is still giving the wrong result, but at least close to the correct numbers. Here is the updated expression:
=((SUM(IIF(Fields!postMisc1.Value,1,0))-SUM(IIF(Fields!preMisc1.Value,1,0)))/(SUM(IIF(Fields!preMisc1.Value,1,0))*100))The new result is -0.006431005....
So, which takes precedence, the desired expression or the desired result? If you're just looking for an expression that gets you the desired result, then just move the *100 so it's the outermost operation, applied to the result of the division.
Cheers!
Thank you Jacob! I think I had been staring at it for too long and was confusing myself. I greatly appreciate the help.
January 27, 2016 at 12:15 pm
bsmith 63193 (1/27/2016)
Jacob Wilkins (1/27/2016)
bsmith 63193 (1/27/2016)
Jacob Wilkins (1/27/2016)
First thing I notice is that your expression doesn't match what you stated in the text. In the text you stated you want to divide by preMisc1*100, but in the expression you have postMisc1 in the denominator.Cheers!
Thank you. I didn't catch that. It is still giving the wrong result, but at least close to the correct numbers. Here is the updated expression:
=((SUM(IIF(Fields!postMisc1.Value,1,0))-SUM(IIF(Fields!preMisc1.Value,1,0)))/(SUM(IIF(Fields!preMisc1.Value,1,0))*100))The new result is -0.006431005....
So, which takes precedence, the desired expression or the desired result? If you're just looking for an expression that gets you the desired result, then just move the *100 so it's the outermost operation, applied to the result of the division.
Cheers!
Thank you Jacob! I think I had been staring at it for too long and was confusing myself. I greatly appreciate the help.
We've all been there 🙂 I'm glad you got what you were looking for!
I'm still interested in what question this calculation is answering; my curiosity (read: paranoia) wants to make sure this is the right calculation for the question.
Cheers!
January 27, 2016 at 12:29 pm
I'm still interested in what question this calculation is answering; my curiosity (read: paranoia) wants to make sure this is the right calculation for the question.
Cheers!
Percentage Increase and Decrease http://www.skillsyouneed.com/num/percent-change.html
January 27, 2016 at 12:32 pm
bsmith 63193 (1/27/2016)
I'm still interested in what question this calculation is answering; my curiosity (read: paranoia) wants to make sure this is the right calculation for the question.
Cheers!
Percentage Increase and Decrease http://www.skillsyouneed.com/num/percent-change.html%5B/quote%5D
Excellent. That's what I suspected, but wanted to make sure.
Cheers!
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply