Search code examples
business-objectswebi

How to create a variable based on two opposing Booleans - business objects web intelligence

I have a cross table in business objects web intelligence which has customer IDs in the rows, items in the columns and quantity in the body. I am trying to create a variable (dimension?) that will flag whether a customer has bought one particular item plus any other item. So, if for example the set item is 'item1' then the flag would show if the customer bought that and also if they bought at least one other item which could be any from another 100 or so. I am trying to write something that is akin to looking at two Booleans returning opposite results in the same statement.

if([item]="item1" And [quantity]>0;"Y";"")

and combine it with something like:

if([item]<>"item1" And [quantity]>0;"Y";"")

At the moment I can only get one or the other working and I can't then seem to create any further variable that would combine them because I end up with "Y" for all rows which is not correct. This is how the table might look after (mock-up in Excel):

enter image description here

I want to then be able to filter based on the flag results variable. Can anyone advise how I might address this problem?

Solution

  • You are really close. I defined count (or quantity) variables with where clauses. I created Var Select Item Count as...

    =[quantity] Where ([item] = [your selected item])

    And Var Other Items Count as...

    =[quantity] Where ([item] <> [your selected item])

    Finally, define your FLAG variable as...

    =If([Var Select Item Count] > 0 And [Var Other Items Count] > 0; "Y")

    Then you can filter on your FLAG variable.

    Certainly, you could embed the code from the first two variables into the last one. However, I like to create my variables such that they build upon one other for easier understanding and maintenance.

    Here is my example with "173" being the selected item.

    enter image description here