4 Replies Latest reply on Jul 2, 2018 9:58 AM by Jim Dehner

    Lookup calculation not working as intended

    Chad Clauson

      Hi my excel file is set up as so:

      Date
      Acknowledged
      Received
      09-Jun90100
      10-Jun95100
      11-Jun92100

       

      I created a calculated field "Effectiveness" which is simply sum([acknowledged])/sum(([received]) and then added a filter for the date range. (So 90% for June 9, 95% for June 10, 92% for June 11)

      I wanted to create a calculated field for my trend arrow (green up arrow if better than previous day, red if down, etc). However it is not working as intended. Currently it keeps returning null according to the legend. I already assigned up, no change, and down arrows based on hard values.

       

      My formula is:

      IF ZN([Acq - Effectiveness]) - LOOKUP(ZN([Acq - Effectiveness]), -1) > 0 THEN "UP"

      ELSEIF ZN([Acq - Effectiveness]) - LOOKUP(ZN([Acq - Effectiveness]), -1) = 0 THEN "NO CHANGE"

      ELSEIF ZN([Acq - Effectiveness]) - LOOKUP(ZN([Acq - Effectiveness]), -1) < 0 THEN "DOWN"

      END

       

      What is my issue?