0 Replies Latest reply on Aug 30, 2012 9:37 AM by E Rayle

    Using aggregate calculation for percent complete

    E Rayle

      I have data as follows:

      • NAME - (String) field in table representing project name
      • EVENT_ID - (long) field in table representing events that have occurred in a project
      • CLASSIFICATION = (String) NULL | species_id
      • calc: HasClassification = IF isNULL([CLASSIFICATION] ) then 0 else 1 end
      • agg calc: Percent of Events Classified = iif( COUNTD([EVENT_ID_1]) > 0 ,WINDOW_SUM(sum([HasClassification])) / WINDOW_COUNT(COUNTD([EVENT_ID_1] )),0)
      • agg calc: Percent of Events Classified 2 = iif( COUNTD([EVENT_ID_1]) > 0 ,sum([HasClassification]) / COUNTD([EVENT_ID_1] ),0)

       

      The table has aliases for the labels.  Each equates to:

      • CNTD(EVENT_ID) - Number of Events
      • SUM(HasClassification) - Number of Events with Species Classification
      • Percent of Events Classified
      • AGG(Percent of Events Classified 2)

       

      To keep the table display simple, I filtered to one project only.  I see the same results when viewing all projects.

       

      My table looks like:

       

      Capture.PNG

       

      The Number of Events and Number of Events with Species Classification are both correct.  The last two columns are my two attempts at calculating what percent of events have been calculated.  What I want is a calculation that would produce the result 414/150094 which equals 0.002758 and would ideally display as 0.28%.

       

      Again, the two formulas I tried are...

      • agg calc: Percent of Events Classified = iif( COUNTD([EVENT_ID_1]) > 0 ,WINDOW_SUM(sum([HasClassification])) / WINDOW_COUNT(COUNTD([EVENT_ID_1] )),0)
      • agg calc: Percent of Events Classified 2 = iif( COUNTD([EVENT_ID_1]) > 0 ,sum([HasClassification]) / COUNTD([EVENT_ID_1] ),0)