topic Equivalent to product() excel function in QlikView in QlikView

Equivalent to product() excel function in QlikView

Fri, 02 Jul 2010 00:19:39 GMT

Hi everyone,

is there any equivalent to the excel product() function in QlikView ?

It would consist in multiplying the values of a field or expression.

For example

Base, Contract, Rate

B1, P1, 0.05

B1, P2, 0.02

B1, P3, 0.01

B2, P4, 0.17

What i would like is to get for the base B1 : 0.05*0.02*0.01=0.00001

One more thing, i don't want to precalculate this indicator during the script execution, i need to do it on the fly

Thanks for your anwers

Richard

Equivalent to product() excel function in QlikView

mike_garcia — Fri, 02 Jul 2010 00:54:53 GMT

Making a quick search through the Help, I did not find something similar to the Product function. However, I was able to obtain the same result using the Concat function like this:

$(=Concat(Number, '*'))

I know it is not made for that purpose, but it works.

Attached is an example. (It is in RAR format because I got an error message saying that qvw files are not allowed...)

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Discussions.Components.Files/11/8103.test-product.rar:550:0]

Mike.

Equivalent to product() excel function in QlikView

Fri, 02 Jul 2010 06:29:49 GMT

Hi Mike,

thank you for your answer.

I've already tried this. It actually works fine but for a small set of values to multiply

My problem is that i have lot of values...

Any other idea ?

Thanks

Richard

Equivalent to product() excel function in QlikView

johnw — Sat, 03 Jul 2010 01:02:37 GMT

I remember another post a long time ago on the same subject. I don't think there was a good answer, unfortunately. It does indicate that it's a recurring requirement, though, so something worth requesting as a new feature if it truly doesn't exist. It does seem a little silly to me to support sum() but not product(). Granted, people need sum() much more often than product(), but they're very similar in flavor.

Equivalent to product() excel function in QlikView

johnw — Sat, 03 Jul 2010 01:05:30 GMT

Wait, no, I found it!

exp(sum(log(Number)))

Makes perfect sense in hindsight. Isn't that exactly how slide rules work? My comment says, "This is the best possible general solution, I think. Thanks to Alexander Schubert for pointing out my dumb."

Equivalent to product() excel function in QlikView

Sat, 03 Jul 2010 15:13:11 GMT

Hi Jonh,

GREAT !!

It works perfectly.

I wish i could remind my maths lessons better...

Thank you and Alexander

Regards

Richard

Equivalent to product() excel function in QlikView

wizardo — Sat, 03 Jul 2010 17:27:39 GMT

hmmm

and what about SUMPRODUCT () function?

how would one do that i QV? 🙂

Equivalent to product() excel function in QlikView

johnw — Tue, 06 Jul 2010 18:01:14 GMT


wizardo wrote:
hmmm
and what about SUMPRODUCT () function?
how would one do that i QV? :)<div></div>

Looks like you'd do it by summing products. Let's look at the example that Excel gives me:

A B C D
1 Array 1 Array 1 Array 2 Array 2
2 3 4 2 7
3 8 6 6 7
4 1 9 5 3

In QlikView, this is probably four fields on a table with three rows. I'll just say the field names are A, B, C and D for simplicity.

SUMPRODUCT(A2:B4, C2:D4)
= 3*2 + 4*7 + 8*6 + 6*7 + 1*5 + 9*3
= (3*2 + 8*6 + 1*5) + (4*7 + 6*7 + 9*3)
= sum(A*C) + sum(B*D)