0 Replies Latest reply: Nov 23, 2015 4:32 AM by Ensar Emirali RSS

    Count distinct with max value

    Ensar Emirali

      Hi fellas,

       

      When I analyze my data with pivot chart, here is my result:

       

      MonthDiff/SourceSetA-01A-02A-04A-05
      -1274116981838291
      024121633
      13372182628571050
      237778452444800
      331177051199696
      43006939775
      523758861080
      62026303
      72010223
      82192456
      91458
      10633
      11909
      12788

       

      With set analysis which result on straight table I want to get is this:

      SourceSetMax MonthDiffAmount of Max
      A-0112788
      A-028456
      A-0451080
      A-053696

       

      For "Amount of max" column, this formula works fine:

      COUNT(DISTINCT IF(AGGR(NODISTINCT MAX(MonthDiff),SourceSet)=MonthDiff,ID)) 
      
      
      

       

      But I want to do this without AGGR function because when adding a new filter to the table with another field, that doesn't work properly.

       

      When I use this formula, it does work for only first row:

      COUNT({$<MonthDiff={'$(=MAX(MonthDiff))'}>} DISTINCT ID)
      

       

      Any idea?

       

      Thanks a lot in advance.