# New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Contributor III

## 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

1 Solution

Accepted Solutions
MVP

## 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

11 Replies
MVP

## 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?

MVP

## 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)

Honored Contributor II

## 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)

Contributor III

## Re: Expression is giving me the wrong results

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

Contributor III

## Re: Expression is giving me the wrong results

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

MVP

## 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

Contributor III

## 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.

Contributor III

## 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.

MVP

## 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