How to multiply?

[andy]

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

[#2]

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

AJ

[hic]

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

[jerem1234]

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!

[andy]

Beautiful!

But it is not defined for values < 0

/Andy

[andy]

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

/Andy

[tresesco]

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