topic A simple maths question in QlikView

A simple maths question

Fri, 13 Nov 2009 22:41:41 GMT

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

A simple maths question

Fri, 13 Nov 2009 22:50:51 GMT

This is not a Qlikview question, but more a BI question...

I mean sum is used to aggregate your data.

In qlikview *, -, / and + are working ...

Regards,

Sébastien

A simple maths question

Fri, 13 Nov 2009 23:00:57 GMT

Hi Spastor,

Sorry to cause confusion, here is the example.

I have Year and month as teh dimenions in graph and sum(Sales) as the expression.

Sales is actually a daily figure. And what QV do here is to add up all daily sales amount and put group the under different year and months.

If I reduce the dimenions by removing Months. All figures shown in table/graph is now yearly figure "ADD" by QV.

My question is, insteads of adding them, can I minus, time or divide them? (data in the same column).

Thanks,

Ivan

A simple maths question

Fri, 13 Nov 2009 23:05:14 GMT

Hum.... I don't really understand what do you want to divide...

Please give me an example if possible.

A simple maths question

Fri, 13 Nov 2009 23:19:54 GMT

Hi,

A bit tricky to explain. Let use multiply as examply.

In the table, I have a field:"survivial rate". So let say our daily dead rate from accident is 0.00001 .Then for a random person, his chance to survive 2 day is 0.99999*0.99999. for one month, we need to multiply everyday surviving chance.

Does it help?

Ivan

A simple maths question

hector — Fri, 13 Nov 2009 23:23:14 GMT

hi, but this is pow(0.99999,2) not a sum()

any ideas?

A simple maths question

Fri, 13 Nov 2009 23:25:18 GMT

pow(sum(1-Rate),count(day)

A simple maths question

Fri, 13 Nov 2009 23:30:44 GMT

Thanks for everyone reply,

But not everyday having the same figures. They can be difference. Therefore, I can't use power.....

Ivan

A simple maths question

johnw — Sat, 14 Nov 2009 02:53:18 GMT

Well, that's annoying. I'd really have expected there to be an expression for the product of matching field values, but I see nothing under aggregation. Searches for "product", "sequence" and "pi" (since a capital pi is how it is expressed) turn up nothing useful either.

So we'll have to fake it. If all of your numbers are 1 or greater:

max(aggr(Number*if(len(above(column(1))),above(column(1)),1),RecNo))

If all of your numbers are between 0 and 1:

min(aggr(Number*if(len(above(column(1))),above(column(1)),1),RecNo))

I can't seem to work out the general case. I'd have expected either of these to work, but the first returns nothing, and the second seems to give me a "random" number from the product sequence rather than the last number:

bottom(aggr(Number*if(len(above(column(1))),above(column(1)),1),RecNo))

subfield(concat(aggr(Number*if(len(above(column(1))),above(column(1)),1),RecNo),','),',',count(RecNo))

AW:A simple maths question

Wed, 18 Nov 2009 23:13:13 GMT

Hi,

for the * there is a solution:#

exp(sum(log(field)))

For the other ones, I have to Think about it.

Regards Alex

A simple maths question

Wed, 18 Nov 2009 23:30:33 GMT

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

AW:A simple maths question

johnw — Wed, 18 Nov 2009 23:35:04 GMT

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!

A simple maths question

johnw — Wed, 18 Nov 2009 23:40:21 GMT


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.)

AW:A simple maths question

Wed, 18 Nov 2009 23:40:58 GMT

Hi again,

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

Regards Alex

A simple maths question

Thu, 19 Nov 2009 00:00:00 GMT

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

A simple maths question

johnw — Thu, 19 Nov 2009 00:22:07 GMT

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.