Expression is giving me the wrong results

[Anonymous]

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

[sunny_talwar]

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?

[sunny_talwar]

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)

[m_woolf]

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)

[Anonymous]

First thing I tried, still don't get what's expected.

[Anonymous]

That's the first thing I tried, but still not the number I need.

[sunny_talwar · Accepted solution]

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

[Anonymous]

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.

[Anonymous]

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.

[sunny_talwar]

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