Set Analysis which counts fields with multiple values

[Chewitt]

Hi, 

Pet Data

I would like a KPI which counts the number of owners that have a cat and a dog, regardless of how many cats or dogs they may have.

The answer should be 3 from this data set.

Everything i have tried, count the owners that have "DOG", "CAT" or "CAT and DOG". I only want "CAT and DOG".

I would prefer not to change my raw data and be able to do everything in the KPI formula.

Thanks in advance.

[Frank_Hartmann]

Maybe like this:

1:
LOAD * INLINE [
    Owner, Pet
    Jenny, dog
    Jenny, cat
    Jenny, dog
    Jenny, parrot
    Ollie, snake
    Ollie, cat
    Michaela, dog
    Michaela, cat
    Michaela, cat
    Manuel,	dog
    Manuel, dog
    Manuel, dog
    Manuel, rabbit
    Manuel, rabbit
    Max, cat
    Max, dog
    Max, dog
    Max, cat
    Thomas, hamster
    Thomas, hamster
    Thomas, dog
    
];

NoConcatenate

2:
Load Owner, concat(distinct Pet,' ',1) as Pet Resident 1 Where Wildmatch(Pet,'cat','dog')  Group by Owner; DROP Table 1;

Textbox Expression:

=aggr(count({<Pet={'cat dog'}>}Owner),Pet)

[Frank_Hartmann]

or this if you want to make the calculation only on frontend:

1:
LOAD * INLINE [
    Owner, Pet
    Jenny, dog
    Jenny, cat
    Jenny, dog
    Jenny, parrot
    Ollie, snake
    Ollie, cat
    Michaela, dog
    Michaela, cat
    Michaela, cat
    Manuel,	dog
    Manuel, dog
    Manuel, dog
    Manuel, rabbit
    Manuel, rabbit
    Max, cat
    Max, dog
    Max, dog
    Max, cat
    Thomas, hamster
    Thomas, hamster
    Thomas, dog    
];

Textbox Expression:

=sum(aggr(if(wildmatch(concat(DISTINCT Pet,' ',1),'*cat dog*'),1,0),Owner))