4 Replies Latest reply on Feb 11, 2019 12:04 PM by Tim Dines

    Determining Percentages of Compliance at Different Levels

    David Mouser


      I'm working with a data set that is largely based on deadlines that vary from activity type to activity type. I can't immediately attach a workbook, so I've outlined a table with my headers and the type of data. I'll see if I can create a dummy file with fake data, but TBD.


      Source String

      OfficeItem IDActivity TypeAssigned DateDue DateCompleted DateDays AllocatedDays to CompleteDays from Target
      Source StringSource StringSource DateSource DateSource Datedatediff Assigned & Duedateiff Assigned & CompleteDays Allocated - Days to Complete


      • I have successfully used the source values to calculate results between dates (the last 3 columns).
      • The various Item IDs may or may not start or end on the same dates.


      What I'd like to accomplish is:

      1. the ability to determine average Days from Target by the Office or Activity level, and
      2. calculate the % of entries (Item ID) that were on time (or early)
        • I'd like this not only at the office or activity level, but also the total aggregate score


      For goal #1 I've tried some LOD expression {fixed [Office],[Activity Type] : avg([Days from Target]) } but with no luck.


      For goal #2 I've setup formula to return results based on days (less than or equal to zero) and provide either 1 or 0 as a result. However, even setting those as string values, I cannot get table calculations to work. Also, not sure if I should be taking a LOD approach for this or not.