topic Calculate PRODUCT on negative values in QlikView

Calculate PRODUCT on negative values

Anonymous — Tue, 07 Jun 2016 14:31:41 GMT

I ended up having a formula which had to apply the PRODUCT function on the daily performances to calculate the aggregated month performance.

The daily performances could be both positive and negative:

Day	Perf
01.01.2016	1%
02.01.2016	-1%
03.01.2016	5%
04.01.2016	-20%
05.01.2016	10%
06.01.2016	6%

I could not use $(=Concat(Perf,'*')) as I was using a Pivot table where for each level the aggregations should have been on different fields.

Exp(Sum(Log(Perf))) works fine only on positive numbers: Log on negative values produces an error and Sum skips these errors.

I'd like to share the formula for finding the PRODUCT of all the numbers, positive and negative included.

The final formula is the following:

Exp( // PRODUCT function

Sum(

Log(

Fabs(Perf) //Treat all values as if they were positive

)

* ( // Determine final sign

mod(

sum(

sign(

sign(Perf) - 1

)

), 2

) * -2 + 1

)

The approach is this: execute the PRODUCT function on all the numbers converted to positive numbers and multiply it to 1 or -1 according to what is necessary.

Function	Explanation
sign(Perf) - 1	Remove positive signs: (-1, 0, 1) - 1 => (-2, -1, 0)
sign( sign(Perf) - 1 )	Convert all negatives to -1; zeros remain 0
sum(...)	Sum up all the negatives to determine if they are odd or even
mod(..., 2)	Check if the final number of negative values is odd or even
mod(..., 2) * -2 + 1	Convert mod result (1 or 0) into (-1 or 1)

The final expression sounds like: PRODUCT([Positive values]) * [-1 if odd negative values or 1 if even negative values].

Is there any better way to doing this?

Re: Calculate PRODUCT on negative values

swuehl — Tue, 07 Jun 2016 15:03:36 GMT

The second part could probably also written as

* If(Mod(Sum(Perf<0),2)=1,-1,1)

Re: Calculate PRODUCT on negative values

Anonymous — Wed, 08 Jun 2016 07:23:17 GMT

Sum should be Count then.

Though I avoid at all cost per record comparisons like sum(perf<0).

Re: Calculate PRODUCT on negative values

swuehl — Wed, 08 Jun 2016 20:01:03 GMT

Valeriy Shylin wrote:

Sum should be Count then.

Though I avoid at all cost per record comparisons like sum(perf<0).

No, I think Sum() should be Sum().

And I believe the Sum(perf<0) is not much different from your Sum(Sign(Perf))

[ignoring the additional Sign() and addition for now].

You can also replace the Mod() with Odd / Even:

* If(Odd(Sum(Perf<0)),-1,1)

I think this expression comes closest to your original pseudo code:

[-1 if odd negative values or 1 if even negative values]

Or if you prefer not to use the if() function:

* (Odd(Sum(Perf<0))*2+1)

I am just trying to suggest an alternative approach.

Regards,

Stefan