topic Re: How to multiply? in QlikView

How to multiply?

andy — Thu, 30 Jan 2014 20:20:24 GMT

Hi folks,

I guess it is time for me to go to bed....

Having this data

KPI	KPIRefPath	PathKPI	weightedValue
3419	3419/3418/3416	3416	0
3419	3419/3418/3416	3418	1
3419	3419/3418/3416	3419	1

showing a nested hiearchy of KPIs.

KPI 3419 refers to 3418 which in turn refers to 3416. The weight of each level being passed shall be multiplied with the value.

So in this case I would like to have the outcome for 3419 of 0*1*1 giving a little table of one row looking like

3419 0

I was looking for a "mul"-function (instead of sum) but without any luck....

LOAD

KPI

mul(weightedValue) as Value

Resident tmp Group by KPI;

Only solution I can think of right now in the very end of the day, but haven't tested, is to concat the weightedValues into the string 0*1*1 and evaluate them but it feels like I'm missing something obvious here.

So how to do it?

/Andy

Re: How to multiply?

Thu, 30 Jan 2014 20:37:10 GMT

Hey Andread,

Please check out the attached file. I created a new field called NewValue which should help you with what you are looking for.

Holler back if you find any issues.

Thanks

Re: How to multiply?

hic — Thu, 30 Jan 2014 20:38:23 GMT

If you want to create an aggregation function that multiplies all records with each other - a Prod() (=Mult()-function) you can do that by summing the logarithms and then use the sum as exponent; using

Exp(Sum(Log( ... )))

HIC

Re: How to multiply?

jerem1234 — Thu, 30 Jan 2014 20:51:29 GMT

Another alternative to what is listed is concatenating all the values with the operator you want and then evaluating like:

Test2:

Load KPI,

Evaluate(concat(weightedValue, '*')) as Product

Resident Test

Group by KPI;

Hope this helps!

Re: How to multiply?

andy — Fri, 31 Jan 2014 09:32:35 GMT

Beautiful!

But it is not defined for values < 0

/Andy

Re: How to multiply?

andy — Fri, 31 Jan 2014 09:38:25 GMT

Yep, That was what I wrote in the initial post, last lines.

/Andy

Re: How to multiply?

tresB — Fri, 31 Jan 2014 09:57:24 GMT

I had a work around Sum = (X1 + X2 + ... + Xn), (X1 * X2 * ... * Xn) = ?