cancel
Showing results for 
Search instead for 
Did you mean: 

Using Where Operator

Former Member
0 Kudos

Hello all,

I'm trying to use the count function to sum two different measures in BusinessObjects webi 3.1.

I need to only add certain elements of the second measure if a certain condition is true, for example:

=Count([Incident Group One])+Count([Incident Group Two])Where(Condition=True)

The dimension containing the condition would be extremely simple, labelling each incident in a group True or False based on a certain test.

I have the dimension containing the condition working, as I have created a simple vertical table to check and the 'True' or 'False' is being applied correctly to each incident.

The problem appears when I try and only count certain incidents in the second group based on where the condition is true. I'm not sure if it is possible to use the Where operator or I need to find a different method.

Any help would be greatly appreciated.

Accepted Solutions (0)

Answers (3)

Answers (3)

Former Member
0 Kudos

Hi all, Thank you for all of your suggestions. I've created the above vertical table showing the incident ID, the item(each INC can have multiple items associated with it) and the condition (which calls says "Yes" if the INC has at least one item associated with it and "No" if it has zero items associated with it.

What I now need is a formula which counts all of the incidents which have a "Yes" next to them, there are fifteen currently. I am using the below formula to count the "Yes" in the bottom right hand cell:

=Count[Incident ID])Where([Condition]="Yes")

However using this exact formula in a stand alone cell counts all of the incidents (24) rather than the ones with "Yes" next to them. Any solutions as I need this information to be available away from this table(ideally I wouldn't even have the table, this was only created to ensure the condition was working correctly).

Again, appreciate all of your help.

Kind Regards

Paul

Former Member
0 Kudos

Hi,

try Count[Incident ID] )Where([Condition]="Yes")) instead

Regards,

Rogerio

Former Member
0 Kudos

That won't work, it's missing an open parenthesis.  I'm pretty sure it has to do with the calculation context, try this: =Count([Incident ID] Where ([Condition]="Yes" foreach ([Incident ID];[Item]) ))

Former Member
0 Kudos

Hi Paul,

Try using

Count([Incident Group Two];All)Where(Condition=True)

Former Member
0 Kudos

Please try

=Count([Incident Group One])+Count([Incident Group Two] Where(Condition=True))


Also refer to below link that shows how to use Where in Count.


Webi - Only count when grather then zero | SCN


Regards

Niraj

Former Member
0 Kudos

Thanks Niraj,

I have tried this and still no luck. The formula is:

=Count([Group One].[Incident ID])+Count([Group Two].[Number of Incidents] Where([Condition]="No")

The condition is simply looking at a count and applying either "Yes" or "No" to each incident based on the result. The condition is working fine, as previously stated. However all I am really trying to do is create a formula to count the number of "Yes" incidents in the group.

Any other suggestions.

Kind Regards

Paul

mhmohammed
Active Contributor
0 Kudos

Hi Paul,

Please attach a snapshot of sample data, so we can help you better.


How about this?

=Sum(If([Condition]="Yes") Then 1 Else 0)

Yeah, this doesn't say anything about the Count of Incidents from Group One or Two, try it first and then we can update it to include Groups One and Two.

Try that and let us know what happens.


Thanks,

Mahboob Mohammed

Former Member
0 Kudos

Hi,

did you try

=Count([Group One].[Incident ID])+if (not([Condition]="No");Count([Group Two].[Number of Incidents] ) ?


Regards,

Rogerio

Former Member
0 Kudos

To count the no of Yes you can refer to below one

=Sum(If [Condition]="Yes" Then 1 else 0)

Also we would like to look at the raw data and the block where you want to display this count to have an idea about the contexts that may impact the results.

Regards

Niraj