5 Replies Latest reply on Sep 15, 2016 4:38 AM by Andrew Watson

    Question on finding aggregation (max) based on distinct variables

    xinyi.ng

      Hi

      I have the following set of data:

       

       

      IDTypeCount
      ACat2
      AMouse1
      ACat4
      BMouse2
      BMouse3
      CCat4
      CMouse5
      CMouse3
      CCat2

       

      I am trying to plot the graphs of the sum of counts at: 1) aggregated ID level, and 2) aggregated for all IDs.

       

      However the complication arises. For type=Cat of the same ID, I need to find the max(count) before I do the aggregation. Else if Type = Mouse, I will aggregate the individual count.

       

      So in the first example, for case ID A, my aggregated count = 4+1 = 5 (since 4=max of 2 and 4 for Type =Cat), for B = 2+3 while C=4+5+3 = 12.

       

      Not sure how I can solve this problem? Sorry I've tried to understand the examples online but it's really confusing. I dont know if I should use windows_max or {fixed : max} or any other functions.

       

      Appreciate any help please!

       

      Regards