Webi formula: Create sum with where clause on measure

[stefan_voltz1]

Hi,

I am trying to create a sum of all values in a table which are greater than a specific threshold. Maybe someone can help me here. I haven't figured whether and how this is possible.

Here is a simple example:

Dimension	Measure
A	12
B	8
C	25
D	3
E	11

I would like to sum up all values that are greater than 10. So I would have 12 + 25 + 11 = 48.

The formula I tried in WebI was:

=Sum([Measure]) Where ([Measure] >= 10)

The formula is validated with no errors, but the result gives me the grand total ignoring the where clause.

In this example, that would be 12 + 8 + 25 + 3 + 11 = 59.

Any idea, how to achieve a formula like the above?

Thanks and regards,

Stefan

[Former Member · Accepted solution]

Hi Stefan,

Write a formula

=Sum([Measure]) Where ([Measure] >= 10 in ([Dimension ]))

Ex:

Below report have currency and their amount

wrote a formula

=Sum([Current Amount Bal]) Where ([Current Amount Bal] > 10,000 In ([Currency]))

Regards,

Mahi

[stefan_voltz1 · Accepted solution]

Hi Mahi,

thanks. Sounds good but unfortunately it doesn't work for me...

Formula, used in the block at the right:

Result:

No idea, what I am doing wrong. We have BO Version 4.1 SP 3, by the way.

Any more ideas?

Cheers,

Stefan

[CarlyThomas · Accepted solution]

Hi Stefan,

If you have aggregation set on Anzahl measure at the semantic layer (Universe) or data source level, this might be why you're seeing a different.

Try:

=Sum([Anzahl] In ([Cluster]) Where ([Anzahl] In ([Cluster]) >= 90))

This will override the default aggregation you may have to avoid summing BEFORE analyzing your WHERE clause.

I hope that helps!

Carly

(had to edit, realized I left in my testing #s for eFashion Sales Revenue)

[stefan_voltz1 · Accepted solution]

Hi Carly,

great, this works!

Thanks a lot!

I played around and figured I can can leave out the reference to [Cluster] in the where clause. So it looks like this now:

=Sum([Anzahl] In ([Cluster]) Where ([Anzahl] >= 90))

There is no specific aggregation setting of the measure on data base level, by the way.

Have a good day,

Stefan

[Former Member]

Hello,

I am trying to create summary report of the detail report .

for e.g I have six details reports in one bo document and they have there total at end respectively and I want to create one main(summary) report which will include only the sum of the six different report.

I was thinking to do it using formula sum() but how to filter it in sum(). like i want to use where clause in sum()