3 Replies Latest reply on Aug 27, 2013 9:19 PM by Joshua Milligan

    Calculation question, counting independant of filter

    barrett fisher

      Hi. I'm sure this is a pretty simple question... but I'm trying to essentially perform a calculation that considers the values of the entire table/column in addition to the values that are in the pill for the criterion.

       

      The formula should essentially be a count of all of the unique values (countd) of the first column (Entity ID in my data set below), and then subtract the count of another set of values where the value is 100%, and then subtract the count where the values are not equal to 100%. The final value set (and where I am having trouble with) is the sum of the unique values in the first column less the values in the first and second calculation. I can get everything to work where I input manually the number of unique iterations of entity ID (ie perform the count myself), but it needs to be scalable.

       

      I.e. Complete = sum(if[% Complete] = 1 then 1 else 0 end)

      Not Complete = sum(if[% Complete]<1 then 1 else 0 end)

      NA = ????

      (where NA is the count of unique values in Column A, less the count of complete and not complete values). In this case, the count of entity ID would be 7 (there are 7 unique entity IDs). For each Workstep ID (the pill I slide onto the rows/col shelf), I want to determine the complete, not complete, and NA counts.

      Example:

      • For workstep AAA for example, the complete count is 6, the incomplete count is 1, and the NA count is 0 (7 - 6 - 1) = 0.
      • For workstep DDD for example, the complete count is 3 and the incomplete count is 0. Consequently, the NA count is 4 (7 - 3 - 0) = 4.

      My challenge (and the crux of the issue) is that I cannot figure out how to calculate the count of the total values (essentially 7 in this case) for the entire dataset as opposed to just the one that shows up in the pill. Does anyone have any guidance in doing this? Again, I can statically set the value to be 7, but I need it to be scalable if I pop in a different dataset.

       

       

      Consider the following data set:

      Entity IDWork Step% Complete
      AF100000AAA0
      AF100000BBB0
      AF100000CCC1
      AF100000DDD1
      AF100000EEE1
      AF100001AAA1
      AF100001BBB1
      AF100001CCC1
      AF100001DDD1
      AF100001EEE1
      AF100001FFF1
      AF100002AAA1
      AF100002BBB1
      AF100002CCC1
      AF100002DDD1
      AF100003AAA1
      AF100003BBB1
      AF100004AAA1
      AF100004BBB1
      AF100005AAA1
      AF100005BBB1
      AF100006AAA1
      AF100006BBB1