topic Re: expression with variable doesn't calculate correct in QlikView

expression with variable doesn't calculate correct

chriscools — Mon, 02 May 2016 13:51:13 GMT

Hello,

i have a expression:

sum ({< ProductStatus = {'Active', 'Einde gamma', 'Sample'}, OrderStatus= {'On hold'}, OnHoldReason={'Treated - waiting for client','Treated - waiting for payment','Untreated'} >} OrderQtyOpen) +

sum ({< ProductStatus = {'Active', 'Einde gamma', 'Sample'}, OrderStatus= {'Placed'}>} OrderQtyOpen)

Then made a variable out of it: eOpenOrdersTotal#

Then used the variable instead of the formula like this:

$(eOpenOrdersTotal#)

Using the expression or the variable i get exactly the same quantities, so that's ok.

Before i would calculate the value of the first expression with the second expression like this:

sum ({< ProductStatus = {'Active', 'Einde gamma', 'Sample'}, OrderStatus= {'On hold'}, OnHoldReason={'Treated - waiting for client','Treated - waiting for payment','Untreated'} >} OrderQtyOpen*SkuPricePurchaseCurrency) +

sum ({< ProductStatus = {'Active', 'Einde gamma', 'Sample'}, OrderStatus= {'Placed'}>} OrderQtyOpen*SkuPricePurchaseCurrency)

It's the same as the first expression but i multiplied with "SkuPricePurchaseCurrency" to get a value.

Instead of making a second variable i calculated like this:

$(eOpenOrdersTotal#) * SkuPricePurchaseCurrency

But the calculation to get the value doesn't give me the same result as when i use the second expression,

and i don't get a total in my pivot table either.

What am i doing wrong?

thanx!

Chris

Re: expression with variable doesn't calculate correct

marcus_sommer — Mon, 02 May 2016 14:02:53 GMT

Both expressions are doing not the same. The first is:

exp1 + exp2

then you multiplied it as variable:

(exp1 + exp2) * Currency

whereby in your expression you used:

exp1 + (exp2 * Currency)

and therefore you get different results.

- Marcus

Re: expression with variable doesn't calculate correct

trdandamudi — Mon, 02 May 2016 14:03:58 GMT

I am assuming that "SkuPricePurchaseCurrency" has some value, for example 200. Try below and see if you get any results:

$(eOpenOrdersTotal#) * 200

Re: expression with variable doesn't calculate correct

chriscools — Mon, 02 May 2016 14:28:41 GMT

Hey Marcus,

thanx for you reply!

the variable is indeed : exp1 + exp2

but the second expression to calculate the value is: (exp1*currency) + (exp2*currency)

that should be the same as : (exp1 + exp2)* currency

and therefore the same as: variable * currency

no? perhaps i'm missing something?

grtz,

chris

Re: expression with variable doesn't calculate correct

chriscools — Mon, 02 May 2016 14:31:21 GMT

Hey Thirumala,

the SkuPricePurchaseCurrency indeed has a value per sku (which is the dimension of the table).

if i change the SkuPricePurchaseCurrency by a value (2) then i get a total for the expression with the variable.

but i still get different values when i also change SkuPricePurchaseCurrency in the second expression.

grtz,

chris

Re: expression with variable doesn't calculate correct

trdandamudi — Mon, 02 May 2016 14:37:55 GMT

Is it possible to post a small sample showing the issue and your expected output, so that we can test it out and come up with a solution.

Re: expression with variable doesn't calculate correct

marcus_sommer — Mon, 02 May 2016 14:44:15 GMT

Ok. I haven't looked carefully enough to see that you multiplied the first expression like the second one. Nevertheless are both expressions not the same. An expression like:

exp1*currency

will be applied on the row-level of your data and an expression like:

(exp1 + exp2)* currency

multiplied the currency on a higher consolidated level and this musn't be the same. For example there could be NULL's on the row-level and also different NULL's on the consolidated level. I think I would use as expression something like this:

rangesum(exp1*currency, exp2*currency)

but it will be depend on your requirements which one provides the correct result.

- Marcus