Equivalent to product() excel function in QlikView

[#1]

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

[mike_garcia]

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.

[#3]

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

[johnw]

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.

[johnw · Accepted solution]

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

[#6]

Hi Jonh,

GREAT !!

It works perfectly.

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

Thank you and Alexander

Regards

Richard

[wizardo]

hmmm

and what about SUMPRODUCT () function?

how would one do that i QV? 🙂

[johnw]

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)