topic Multiple field values in one column in QlikView

Multiple field values in one column

naveen341 — Wed, 25 Nov 2020 16:16:04 GMT

HOw can we multiply field values in one column in Qlikview expression

example

Expected o/p is the product of all numerators: 34*67*86

ID	Numerator	Expected O/P
13435	34	195908
4646	67	195908
6337	86	195908

Thanks in Advance

Re: Multiple field values in one column

sunny_talwar — Wed, 28 Nov 2018 19:23:29 GMT

Try this

=exp(Sum(log(Numerator)))

Re: Multiple field values in one column

naveen341 — Wed, 28 Nov 2018 19:42:27 GMT

Thanks sum this helped.

One more thing

How do i get (34*64*87) power 1/ count(Numerator)

Re: Multiple field values in one column

sunny_talwar — Wed, 28 Nov 2018 19:45:22 GMT

This

=Pow(exp(Sum(log(Numerator))), (1/Count(Numerator)))

Re: Multiple field values in one column

naveen341 — Wed, 28 Nov 2018 20:03:58 GMT

Hey sunny if i have many values exp(Sum(log(Numerator))) is showing inft.
is there any other way we can find product

Re: Multiple field values in one column

sunny_talwar — Wed, 28 Nov 2018 20:14:49 GMT

Any calculation which gives inft. based on the above expression.... will give inft. based on other methods as well...

Out of curiosity, what are you multiplying... can you share a snapshot?

Re: Multiple field values in one column

naveen341 — Wed, 28 Nov 2018 20:18:38 GMT

Please find the attached screen shot i want to multiply all the values in second row

Re: Multiple field values in one column

sunny_talwar — Wed, 28 Nov 2018 20:23:38 GMT

I am not seeing infinity for either of the expressions I mentioned above

Are you sure you are using the expression correctly?

Re: Multiple field values in one column

naveen341 — Wed, 28 Nov 2018 20:30:12 GMT

This is the expression i am using

Re: Multiple field values in one column

sunny_talwar — Wed, 28 Nov 2018 20:35:34 GMT

If you export this chart into Excel and perform this multiplication in Excel, are you able to see a result? My guess is that you have a lot of rows and multiplication can very quickly become a very very big number... a number which QlikView (or even Excel) may not be able to display.

But this is just my theory, let's see what other experts have to say about this

@rwunderlich

@marcus_sommer

@Oleg_Troyansky

Re: Multiple field values in one column

sunny_talwar — Wed, 28 Nov 2018 20:44:39 GMT

Seem like this is the maximum you can get

Table:
LOAD * INLINE [
    ID, Numerator
    2, 10000000000000
]
while IterNo() <= 23;

So, basically I am multiplying 10,000,000,000,000 ---- 23 times and it still works...

If I do

Table:
LOAD * INLINE [
    ID, Numerator
    2, 10000000000000
]
while IterNo() <= 24;

I see Inf.

Re: Multiple field values in one column

naveen341 — Wed, 28 Nov 2018 21:00:52 GMT

It would be great if we can get any work around for these problem.

@rwunderlich

@marcus_sommer

@Oleg_Troyansky

Re: Multiple field values in one column

marcus_sommer — Thu, 29 Nov 2018 08:39:03 GMT

Depending to the used number-system of Qlik (this from Excel is quite similar) are the numbers of digits with which Qlik could calculate and also displaying (there are differences between it) restricted. This was an intentional choice to ensure a very good calculation-performance and the most BI scenarios benefit from it because they need to calculate often thousands or even millions of calculations at the same time but rather less calculations with really big numbers and/or a very high precision. Some more background to it could you find here: Rounding-Errors.

Therefore I assume that Sunny is right and your numbers are just too big. If you follow his suggestion to perform it in Excel you could check it quite quickly. This means if you really need these calculation you need to use a different tool for it whereby by using Qlik with a R connector or something similar it might be possible to keep it within the Qlik application.

If you describe what the aim is behind this calculation there might be other ways to solve it.

- Marcus