1 Reply Latest reply on Dec 19, 2018 4:21 PM by Patrick Van Der Hyde

    Difference between 2 Values




      I am trying to do a couple of things with the workbook attached.


      Sheet 1:

      1. The calculation standard v actual, does not seem to be working. I am essentially trying to take the productivity standard and then get the difference between the productivity standard and the unique user by day number.
      2. Once step 1 is complete, I need to have the standard v actual number, show up as a KPI (check mark or X)


      Sheet 2:

      1. I am trying to get the difference between the most recent week and the week prior as the final column on the table.
      2. I would then like to have this show as a KPI (check mark or X)


      Thank you,


        • 1. Re: Difference between 2 Values
          Patrick Van Der Hyde

          Hello Geryn,


          Your Productivity Standard has multiple rows and when it is aggregated with the sum() function - the value is higher than the Dimension being returned in the left most side of the view.  For instance Andrea has a sum(productivity standard) of 6.  To fix this use avg() instead and then the calculations work out.


          So getting a checkmark to appear per day will require a fair bit of work - you can see examples here - Older But Still Useful – Conditional Formatting | Drawing with Numbers


          As for the calculation of the last two values.  This will require some help with Table Calculations to select the values for the last column -> [Last value in row]

          ifnull(window_avg(if last()=0 then sum([Unique User By Day ]) end),0)


          and a calculation for [2nd to Last value in row]

          ifnull(window_avg(if last()=1 then sum([Unique User By Day ]) end),0)


          and then a calculation to find the difference and just display that -  [Diff to two last values].

          if last()=0 then

          window_avg([Last value in row]-[2nd to Last value in row])

          else null



          These are advanced Tablea Calculations because we have to pull out the value from the respective columns, then represent these values across every week in the view (done with the  if last()=0 or if last()=1)  and then finally running a calculation of the first minus the second and averaging taht value so it represents one instance instead of a sum of all columns. 


          I believe this was originally uploaded with version 10.1 which is not supported so I have saved this for you in version 10.2 - the oldest I have installed on my pc.