2 Replies Latest reply on Jul 4, 2018 8:00 AM by Russell Wotherspoon

    Filtering based on max of group by data

    Russell Wotherspoon

      Hi All,

       

      I have a data set pulled from SQL which is similar to the below. This is order transaction log info.

       

      OrderID,State,Counter

      A,NEW,1

      A,PART_FILLED,2

      A,FILLED,3

      B,NEW,1

      B,PART_FILLED,2

      B,CANCELLED,3

      C,NEW,1

      C,CANCELLED,2

       

      I want to set up a view that shows counts of final states of the orders.

      This is pseudo calculated by something like "Count of State where max(Counter) , group by OrderID".

       

      In the example above this would then show me 1 FILLED, 2 CANCELLED.

       

      I've tried to use an LOD calculation like {FIXED[OrderId]:MAX([Counter])} as a filter but this just gives me the value of the counter rather than the State.

       

      I could do this in the SQL import which would create a subset of the data but for other sheets I want the whole transaction info so don't want to filter at that level.

       

      Any ideas?

       

      Thanks,

      Russ