Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I want to multiply 3 values that exist in one column. Value x, the value above (y) , and the value above that one (z).
I'm able to sum these using Rangesum and above. But how should I multiply them x*y*z.
,KR Koen
Hello,
Works like a charm. Had seen a very similar suggestion of Gysbrecht.
In my final solution, I actually made myself a RangeProduct function using a variable with parameters.
variable name: eRangeProduct
variable definition: Num(exp( Rangesum(Above( log(fabs($1) ),0, $2 )) ) ,'#.##0,##', ',', '.')
Expression in my chart: $(eRangeProduct( MF, 3)
or if you want to have a rangeproduct of all the values in a column: $(eRangeProduct([Fieldname], RownNo()).
,KR Koen
I found this solution.
Pick( RangeCount( Above(MF,1,6)) + 1
, MF
, MF * Above(MF, 1)
, MF * Above(MF, 1) * Above(MF, 2)
, MF * Above(MF, 1) * Above(MF, 2) * Above(MF, 3)
, MF * Above(MF, 1) * Above(MF, 2) * Above(MF, 3) * Above(MF, 4)
)
Though its not that ellegant. Any better ideas?
What about using Peek?
Peek(field_name[, row_no[, table_name ] ] )
You could try something like Peek(field_name,RowNo()-1,table_name)
Not sure if this is a one time calculation but if not you could use the RowNo function instead of specifying a row.
I haven't tested this but I think something like this could work.
If you want to get the product of multiple values in the same field , maybe
=if(even(count({<FIELD={"<0"}>} FIELD)),1,-1)*exp(sum(log(fabs(FIELD))))
edit: so when you want to use it in a chart, maybe soemthing like
=exp( Rangesum(Above( log(fabs(YourExpression) ),0, Rowno() )) )
The exp(sum(log())) trick makes me so happy - hit that problem with a slide rule!
Hello,
Works like a charm. Had seen a very similar suggestion of Gysbrecht.
In my final solution, I actually made myself a RangeProduct function using a variable with parameters.
variable name: eRangeProduct
variable definition: Num(exp( Rangesum(Above( log(fabs($1) ),0, $2 )) ) ,'#.##0,##', ',', '.')
Expression in my chart: $(eRangeProduct( MF, 3)
or if you want to have a rangeproduct of all the values in a column: $(eRangeProduct([Fieldname], RownNo()).
,KR Koen
Very elegant. is this doable at the script level?