Sumproduct()

building reports webintelligence
1

Is there an equivilant of sumproduct which is an excel formula in webi? thanks

showt2 (BOB member since 2009-05-11)

2

I do not believe there is a function like this in webi not sure about deski. You will probably have to multiply the individual rows/columns together and then sum the results of each product for the block.

jbarnes794 :us: (BOB member since 2008-06-13)

3

And check also RunningProduct() function if it can help in some way.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

4

How would that work, can you kindly explain. How can you add individual cells in webi? I know in excel its possible but webi?

showt2 (BOB member since 2009-05-11)

5

It is hard to come up with exactly how without knowing how your data is returned but if your data is returned such that each row of your data set has each element of your arrays you want to sumproduct together like this:

OrgCD C1 C2 C3 C4
OrgId1 3 4 2 7
OrgId2 8 6 6 7
OrgId3 1 9 5 3

Where “Array 1” is the data in columns C1 and C2 and “Array 2” is the data in columns C3 and C4 then you could set up the following variables to get the sumproduct.

Product1=C1C3 (this is calculated at the row level)
Product2=C2C4 (this is calculated at the row level)

Sumproduct=sum(Product1) in block + sum(Product2) in block

with the context of in Block it would sum up the individual row products to get an overall sum.

jbarnes794 :us: (BOB member since 2008-06-13)

6

Hey Thanks for the response, but im a still bit confused as to how i would apply to my scenario… so i have 2 columns first column is a simple # column e.g.(1,2,3,4) and the 2nd column is a Percentage column. Now what i have to do is do the sumproduct of those two columns. Being that its a % column the calc. are coming out wrong when i try to sum them. i am currently doing Sum(column 1 * Column2).

showt2 (BOB member since 2009-05-11)

7

When you say it is a percentage, did you multiply by 100 to get it to be a percentage or did you just set the format on the column to be a percentage?

In other words do you need to multiple the first column times the actual percentage value for example 59.3 or multiply by the ratio number .593?

I would setup a variable that calculates the products such as:

Column1 * Column2*100

Multiply by 100 if you need to. Add this to your block to check and make sure the product is working.

Then setup another variable to calculate the sum of the products as:

sum(column1 * column2*100) in block

Again include the 100 if you need to. When you add this to your block you should get the same value on each row.

jbarnes794 :us: (BOB member since 2008-06-13)

8

Hey, i tried the *100 but i didn’t work, my % column is achieved via the webi option for ‘percentage’ so all the formatting is done automatically.

showt2 (BOB member since 2009-05-11)

9

Attached is an example of what I think you are trying to do and below are the equations I am using.

Org Id - is a field from the database
Column 1 - is a numeric number associated with the org id and is in the database
Column 2 - has the following formula where charges is measure from the database

[Percent Charges]=Percentage([Charges])

The Product of Column1 and Column2 is in the fourth column and has the following formula.

[Product]=Column 1]*[Percent Charges]*100

Sum of All Products column has the following formula:

[Sum of Product]=Sum([Product]) In Block

Is this what you are trying to do?
Test Sum Product.xls (23.0 KB)

jbarnes794 :us: (BOB member since 2008-06-13)

10

Thanks for the attachement but thats exactly how i was doing but i get a much bigger number then im suppose to. :hb:

showt2 (BOB member since 2009-05-11)