I would like to create a column in a straight table to identify uniqueness. For example, I have apples, bananas & pears. I also have the names of people that bought them.
How can I calculate against each fruit how many people only bought that fruit?
Thank you Richard. The results I got at first with your suggestion seemed wrong, so I incorrectly thought that you had got the fields the wrong way round.
In fact all that was missing was a distinct:
sum(if(aggr(count(distinctFruit),Name)=1,1)) did the trick.
Thank you for all your responses, but I am obviously missing something.
My straight table has Fruit as it's dimension.
if I do aggr(count(distinct Name), Fruit), I get the same as count(distinct Name), which is the number of people who bought this fruit.
What was the aggr function supposed to add to this?
If 2 people bought Apples and 3 bought Pears, but only one bought both, then I want to return 1 against Apples and 2 against Pears, that being the unique purchasers of each fruit.
If the Fruit is the dimension then aggr by fruit won't make any difference to the sum. In my example its aggr by Name and only counts if the count of fruit is 1. Ie. A person only bought one fruit (that fruit) and no others
Can you feedback on my example? I thought reading your question the result was correct?
Thank you Richard. The results I got at first with your suggestion seemed wrong, so I incorrectly thought that you had got the fields the wrong way round.
In fact all that was missing was a distinct:
sum(if(aggr(count(distinctFruit),Name)=1,1)) did the trick.
