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

Determining the last actual value...

Hi all.

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

 Week 1 2 3 4 5 6 7 8 9 10 Value 1234 2345 1265 1278 2874 6712 0 0 0 0

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

• 1. Re: Determining the last actual value...

Andrew,

I've attached a workbook that includes some inline commentary.  Basically, I used a couple of table calculations to work backwards along week and determine the "first" good value (which is really the last).

Regards,

Joshua

• 2. Re: Determining the last actual value...

Thanks for that Joshua - wow that was really non-self evident but I can totally follow what you've done. Worked a treat on the data set and it does exactly what I need it to. Thanks loads.

Cheers

Andrew