5 Replies Latest reply: Nov 28, 2017 11:21 PM by Sunny Talwar RSS

    Set Expression Question

    Andrew Sumner

      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!