Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
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.
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)
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))