A simple maths question

[#1]

Hi,

Sorry to ask a simple question like that, but I am a bit lost....

If I want to sum up data in a field, I use sum(field). How about *,- and / (times, minus and divide) the field??

Thanks,

Ivan

[#2]

Hi,

Thanks for the suggested answer. It is a right and simiar to what I have done. However, I can only do the * as well....

Once again, thanks.

Ivan

[johnw]

Doh! How did I forget that you could use logarithms to turn a multiplication problem into an addition problem? I blame the lack of slide rules! The kids these days with their "calculators" and "computers" - they forget all their maths!

[johnw]

ivanyeung wrote:However, I can only do the * as well....

OK, I'm not sure exactly what you mean by minus or divide in a case like this. If you subtract EVERY term, that's just -sum(field). Are you subtacting every other term or something? I guess I don't understand the requirement. As for division, are you starting with 1, and then dividing by every field in sequence? That's the same thing as multiplying by 1/field, so exp(sum(log(1/field))), I think?

(Edit: I continue to be dense. Exp(sum(log(1/field))) is the same as 1/exp(sum(log(field))), which would be faster to execute.)

[#5]

Hi again,

for the minus Problem, is sum(field*-1) what you want??

Regards Alex

[#6]

Oh,

Yeah, how possible I know to use log, but forgot to use 1/field. It will give me '/'. Thanks.

Actually,what I need are '*' and '/'. Most of my data are time-series data. And I need to do some calculations which are not build-in by Qlikview. A simple example is the accumlated return on investment (compound return).

Also, I need to do use moving average (not exactly, but similar). E.g. Value at time (t) +/- Value at time (t-1) then divide by something across the whole time-period etc.

Thanks you very much for everyone's help!!

Ivan

[johnw]

In case this matters to anyone, I did some testing with "large" sequences of numbers very close to 1. On my antiquated machine, I'm still under half a second to multiply a million numbers, and about a second and a half to multiply ten million numbers. That seems pretty good to me. In any case, I doubt it would be possible to write an expression faster than the one Alex presented, since that's about as straightforward as things can get.

In any case, thanks Alex. I don't have any immediate need for this, but it's nice to have another useful tool available.