I'm still learning set expressions along with aggr, and find myself easily stumped by problems that I think should be easily solved. Having spent the better part of a day on this one & getting nowhere, I thought it best to ask for help!
My purely fictitious test dataset is as follows:-
Shelves:
load * inline [
InventoryMonth,ShelfNumber,Fruit
2,1,Apple
2,1,Orange
2,1,Pear
2,2,Banana
3,1,Apple
3,1,Orange
3,2,Banana
3,3,Apple
3,2,Persimmon
3,2,Pomegranate
3,2,Passionfruit
4,1,Pear
];
The chart needs to display the count of shelves that had more than X types of fruit on them during the month.
Let's say X >= 2. For month=2, only ShelfNumber 1 met that criteria, so the value on the chart should be 1. For month=3, ShelfNumber 1 and 2 both had 2 or more types of fruit, so the chart would show 2. Finally for month=4, the value would be zero.
I've tried numerous approaches to this, none of which seem to be close to working. I expected a simple solution such as the following to work...
count(distinct {<ShelfNumber = {'=count(distinct Fruit) >= 2'}>} ShelfNumber)
... however it returns values of 2, 2, 1 instead of the expected 1, 2, 0
Any assistance would be much appreciated!
