SAP Web Intelligence - Summary Column Based on Multiple Criteria

Question

I'm trying to create a report with a summary column based on multiple criteria. Below is my desired output in Excel as an example. I am having trouble coming up with ways to create this summary column (col H)

Link to the example here.

Essentially, I need column H to do the following:

Score = 0

For each cell in Range C:G, if cell isn't empty, get amount of points test is worth based on region the user is in, and add that score to "Score", and show in Column H as a total at the end.

Is this possible in SAP WI? I really really appreciate any help with this (even a push in the right direction).

Thanks!

Answer 1

I believe you're looking for a compounded IF function in a Variable here.

If you're used to using Excel, you will recognise the similar syntax for it:

=IF(TEST;Value if true;Value if false)

You will want to compound that so that you have and IF based on the 'Region' followed by a list of IF statements for each test that add up the points, followed by another list of IF statements for each test based on the other 'Region'

Something like the following should give you the basic start:

=If([Region]="Oceania";If(IsNull([Test #1]);0;1)+If(IsNull([Test #2]);0;1)+IF(...);If(IsNull([Test #1]);0;2)+If(IsNull([Test #2]);0;0.5)+IF(...)) 

From there you add the relevant test columns into the sums to get the totals for each row.