topic Dividing variables bring wrong values in QlikView

Dividing variables bring wrong values

madnanansari — Thu, 27 Apr 2017 07:53:14 GMT

I have defined the below variables in the system:

vSales1

vSales2

vTickets1

vTickets2

vSales = $(vSales1) + $(vSales2)

vTickets = $(vTickets1) + $(vTickets2)

AverageSales = ($(vSales1) + $(vSales2)) / ($(vTickets1)+$(vTickets2))

bring rights results.

where as the below formula brings wrong results:

AverageSales = $(vSales) / $(vTickets)

any idea how to resolve this?

Re: Dividing variables bring wrong values

prieper — Thu, 27 Apr 2017 08:39:40 GMT

any NULL-values involved?

Peter

Re: Dividing variables bring wrong values

prachisangewar — Thu, 27 Apr 2017 09:11:24 GMT

Hi,

Try to define the variables as below:

vSales = vSales1 + vSales2

vTickets = vTickets1 + vTickets2

(remove the '$')

You should get the correct result on AverageSales = $(vSales) / $(vTickets) .

Re: Dividing variables bring wrong values

Frank_Hartmann — Thu, 27 Apr 2017 10:02:03 GMT

i tried to rebuild your issue and for me it works fine.

see attached sample.

hope this helps

Re: Dividing variables bring wrong values

Frank_Hartmann — Thu, 27 Apr 2017 10:06:48 GMT

I guess you missed the "=" signs

hope this helps

Re: Dividing variables bring wrong values

rwunderlich — Thu, 27 Apr 2017 11:16:51 GMT

I think you have a problem with order of operations. The divide is being done first. Adds parens to control the order.

vSales = ($(vSales1) + $(vSales2))

vTickets = ($(vTickets1) + $(vTickets2))

-Rob

Re: Dividing variables bring wrong values

Peter_Cammaert — Thu, 27 Apr 2017 12:09:25 GMT

To easily detect what ROb says is going wrong, try to perform the $-sign substitution yourself and see whether you get wrong precedence or skipped/missing operands in your calculations.

If I do the substitution manually, I get an almost-final expression of = $(vSales) / $(vTickets) into

= $(vSales1) + $(vSales2) / $(vTickets1) + $(vTickets2)

which means that only $(vSales2) (or a part of that variable) is divided by $(vTickets1) because of operator precedence.

Another time-saving suggestion: when using $-sign substitution to assemble expressions, always use too many parentheses. They don't cost you anything but will save you many hours of searching and debugging.

[Edit] Sorry, corrected first name spelling

Re: Dividing variables bring wrong values

madnanansari — Thu, 27 Apr 2017 12:28:37 GMT

no null values

Re: Dividing variables bring wrong values

madnanansari — Thu, 27 Apr 2017 12:49:14 GMT

Sorry I wrongly marked this as correct answer. This does not help.

Re: Dividing variables bring wrong values

madnanansari — Thu, 27 Apr 2017 13:22:46 GMT

$(vSales) and $(vTickets) give the right answer whereas $(vSales) / $(vTickets) bring wrong answer.

Re: Dividing variables bring wrong values

madnanansari — Thu, 27 Apr 2017 13:23:26 GMT

it brings all the column having the same values. Infact I am using Qlik Sense but posted here in Qlik View. Sorry for that.

Re: Dividing variables bring wrong values

madnanansari — Thu, 27 Apr 2017 13:24:12 GMT

dont work. Infact I am using Qlik Sense instead of Qlik View. Sorry for that.

Re: Dividing variables bring wrong values

madnanansari — Thu, 27 Apr 2017 13:24:32 GMT

no this is not the case.

Re: Dividing variables bring wrong values

Peter_Cammaert — Thu, 27 Apr 2017 16:06:47 GMT

Please change the forum by following the instructions given here: QlikCommunity Tip: How to move your discussion thread

Re: Dividing variables bring wrong values

Peter_Cammaert — Thu, 27 Apr 2017 16:31:20 GMT

Note that in QlikView objects, $-sign substitution is performed before the object is recalculated. And cannot change row-by-row.

Are the vSales1 & 2 and vTickets1 & 2 variables dependent on something that should change in every row? What do they contain?

Re: Dividing variables bring wrong values

julioarriaga — Wed, 28 Nov 2018 01:55:19 GMT

Thanks Rob, but isn't misleading this operation order? I don't see any case in which we would want to divide first the last element of a dollar expression before finishing the evaluation.