topic Re: Set Analysis which counts fields with multiple values in App Development

Set Analysis which counts fields with multiple values

Chewitt — Sat, 16 Nov 2024 05:08:08 GMT

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.

Re: Set Analysis which counts fields with multiple values

Frank_Hartmann — Mon, 29 Jul 2019 16:58:01 GMT

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)

Re: Set Analysis which counts fields with multiple values

Frank_Hartmann — Mon, 29 Jul 2019 17:40:57 GMT

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

Textbox Expression:

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