topic Multiplication / Product in QlikView

Multiplication / Product

Wed, 20 Oct 2010 09:29:05 GMT

Hi,

I would like to multiply a field, in the same manner as you can sum a field: SUM( [Field] )

I could not find a function such as this "PRODUCT"( [Field] ). If Field = {1,1,5,7}, then I would expect such a function to return: 1*1*5*7=35, just as the SUM function returns: 1+1+5+7=14.

Is there such a function...?

/regards Erik

Multiplication / Product

johnw — Thu, 21 Oct 2010 00:14:56 GMT

If the numbers are all positive (from Alexander Schubert):

exp(sum(log(x)))

If you might have zeros or negatives (from Haider.Al-Seady):

if(count(if(x=0,1))>0, 0, // try if there are zeros
if(even(count(if(x<0,1))),1,-1)* // find "cumulative" sign
exp(sum(log(fabs(x)))) // Abs product
)

Which translates to this more efficient set analysis version, I believe:

if(count({<x={0}>} x),0
,if(even(count({<x={"<0"}>} x)),1,-1)
*exp(sum(log(fabs(x)))))

And there's an open proposal to have a separate function for this on the customer portal if you have access and would like to have such a function:

https://emea.salesforce.com/ideas/viewIdea.apexp?id=08720000000HKtd&srPos=0&srKp=087

Multiplication / Product

johnw — Thu, 21 Oct 2010 01:24:57 GMT

Slightly more efficient and seems to work with zeroes even though not explicitly checked:

if(even(count({<Number={"<0"}>} Number)),1,-1)*exp(sum(log(fabs(Number))))

Multiplication / Product

Tue, 26 Oct 2010 10:15:37 GMT

Thanks,

already tried the variant "exp(sum(log(x)))", and it works well! Negative numbers are fortunately not applicable, so did not have to use that solution.

Don't think I have acces to the salesforce portal, but I definately think there should be a function "PRODUCT".

/regards Erik