2 Replies Latest reply on Apr 11, 2013 11:23 PM by Andrew Fisher

    Determining the last actual value...

    Andrew Fisher

      Hi all.

       

      I have a data set - simplified it looks like this:

       

      Week12345678910
      Value1234234512651278287467120000











       

      I am trying to create a calculated field that allows me to determine if a value is the last value in the data set. You'll note that even though my weeks run to 10 my values only run to week 6. As such my output should be that week 6 is "True" for being the last week that has an actual value.

       

      Note that the values for week 7 - 10 are **actually** zero with multiple records in them, not nulls with nothing in there.

       

      I've done something like this before but it didn't account for the zeros in the latter weeks. The code for that is:

       

      if (WINDOW_MAX(attr([week_number])) = attr([week_number])) then

          "True"

      else

          "False"

      end

       

      Now of course - this gives me "False" for weeks 1-9 and "True" for week 10.

       

      What do I need to do to get it so it gives me True for the last week in the set that is not zero. If I was using a "real" programming language this would be trivial because you simply set everything to false as a default then just iterate backwards to find the first non-zero entry and set it True then return. In tableau I can't seem to grasp the right way to attack this.

       

      Cheers

      Andrew