11 Replies Latest reply: Feb 11, 2016 10:29 AM by Stefan Wühl

Expression is giving me the wrong results

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

• Re: Expression is giving me the wrong results

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?

• Re: Expression is giving me the wrong results

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)

• Re: Expression is giving me the wrong results

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

• Re: Expression is giving me the wrong results

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

• Re: Expression is giving me the wrong results

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.

• Re: Expression is giving me the wrong results

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

• Re: Expression is giving me the wrong results

Exactly, that's why it was driving me crazy lol.

• Re: Expression is giving me the wrong results

Sunny T wrote:

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

Seems like an associative data model breaks associative laws

• Re: Expression is giving me the wrong results

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)

• Re: Expression is giving me the wrong results

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

• Re: Expression is giving me the wrong results

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.