Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Guys,
I've got a calculation in EXCEL that is -14867/-3245 that gives me a result of 458.1%.
But when I do the same calculation in QS, I'm getting something completely different.64239357%
Here's the expression
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}
,NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
-
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}
,NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
/
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}
,NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
+
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}
,NEW_PRODUCT_FAMILY_DESC={"BBB"}>}[BBB Value PQ]),5)/1000
-
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}
,NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
+
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}
,NEW_PRODUCT_FAMILY_DESC={"BBB"}>}[BBB Value PQ]),5)/1000
Thanks in advance
What is the order of things here: Is it
(A-B)/(A + C - B + D)
or is it this?
(A-B)/((A+C) - (B+D))
Assuming each letter corresponds to your each Ceil() expression
Not sure how to check since I don't have the application or Excel or the context. Would you be able to provide the sample application or the Excel?
Just a after thought, may be try this:
(Ceil(Sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}, NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
-
Ceil(Sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}, NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000)
/
(Ceil(Sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}, NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
+
Ceil(Sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}, NEW_PRODUCT_FAMILY_DESC={"BBB"}>}[BBB Value PQ]),5)/1000
-
Ceil(Sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}, NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
+
Ceil(Sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}, NEW_PRODUCT_FAMILY_DESC={"BBB"}>}[BBB Value PQ]),5)/1000)
Don't you need parentheses around your Numerator and Denominator?
(ceil(sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}
,NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
-
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}
,NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000)
/
(ceil(sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}
,NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
+
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious5)','$(vMonthPrevious3)','$(vMonthPrevious4)'}
,NEW_PRODUCT_FAMILY_DESC={"BBB"}>}[BBB Value PQ]),5)/1000
-
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}
,NEW_PRODUCT_FAMILY_DESC={"AAA"}>}[AAA Value PQ]),5)/1000
+
ceil(sum({<[SALES MONTH]={'$(vMonthPrevious6)','$(vMonthPrevious7)','$(vMonthPrevious8)'}
,NEW_PRODUCT_FAMILY_DESC={"BBB"}>}[BBB Value PQ]),5)/1000)
First thing I tried, still don't get what's expected.
That's the first thing I tried, but still not the number I need.
What is the order of things here: Is it
(A-B)/(A + C - B + D)
or is it this?
(A-B)/((A+C) - (B+D))
Assuming each letter corresponds to your each Ceil() expression
I get -2.31% with the parentheses, the correct answer that excel gives, even if I hardcode the figures in QS expression is suppose to be
458.2%.
I've done 100's of percentages in QS in this project and it's only this one is throwing a wobbly.
Cheers, I just needed that way of looking at it
I was doing this
((A-A)/((A+B)-(A+B)))
It needed to be this
(A-A) / ((A+B)-(A+B))
Thanks, sometimes when you ask the question, you see straight away where the problem was.
Hahahaha, I don't see much different between the two lines of code you have posted
I was doing this
((A-A)/((A+B)-(A+B)))
It needed to be this
(A-A) / ((A+B)-(A+B))
But as long as you get what you were looking for, it is all good