Count keys not containing atleast one value in a one to many relationship

Nixxus 0
Viewed 15

My apologies for the nonsense example, the actual data i'm working with cannot be shared.

I have a set of customer baskets containing items forming a one to many relationship. In BO this results in a table with the basket ID duplicated for each item within that basket.

e.g. | ID | Item | |----|--------| | 1 | Apple | | 2 | Apple | | 2 | Orange | | 3 | Apple | | 3 | Orange | | 3 | Pear |

I'm attempting to count the number of baskets that do not contain a pear, however the formulas

=Count([ID]) Where (Not([Item] ="Pear")) and =Count([ID]) Where (Count([Item]="Pear")<1) Both return incorrect results as the initial count can find other rows in the table where basket 3 does fit the conditions.

Is there a formula I can use to check all items relating to basket before counting to return my desired output of "2". I'm trying to avoid breaking up my Data query if at all possible.

Cheers,

business-objects
1 Answers

The basic concept is to duplicate your query with additional criteria to only return the baskets with pears, left join to it, and count the IDs that are in your original, but not in the duplicated query.

Begin by duplicating your query adding criteria so you only get the baskets with pears.

enter image description here

Next, merge the ID dimensions for both queries.

enter image description here

Create a variable with the Qualification set to "Detail", the Associated dimension as the Merged ID dimension, and the formula as the Item from your duplicated query.

enter image description here

Finally, create a Var Number of Baskets With No Pears variable to count the IDs from your original query where the detail variable you just created is null

=Count([Query 1].[ID]) Where (IsNull([Var Query 2 Item]))

There you have it.

enter image description here

userPzE20
Related