8 Replies Latest reply on Nov 19, 2014 12:36 PM by Matt Lutton

# Lookup function to find the Last Value of a Previous partition?

Can someone help with a table calculation that uses the Lookup function to find the Last Value of a Previous partition?

I am able to get the Last value of the same partition (See Below), but I need the last value of the Previous Partition.

LOOKUP(SUM([Value]),Last())

• ###### 1. Re: Lookup function to find the Last Value of a Previous partition?

Can you post an example TWBX so we can build on that?  Cheers

• ###### 2. Re: Lookup function to find the Last Value of a Previous partition?

Basically for each data point, I want to have a corresponding data point that references or Looks Up to the December point from the previous year.  I.E Jan 2012 looks up to Dec 2011 and Feb 2012 looks up to Dec 2011.

When Partitioning by Year, the point i am looking for is the Last Point of the previous partition.

• ###### 3. Re: Lookup function to find the Last Value of a Previous partition?

Can you create/illustrate a mockup of your intended results, to be 100% clear?  Thanks!

• ###### 4. Re: Lookup function to find the Last Value of a Previous partition?
 Date Year of Date Month of Date Value Lookup Value 12/1/2011 2011 December 100 1/1/2012 2012 January 200 100 2/1/2012 2012 February 200 100 3/1/2012 2012 March 200 100 4/1/2012 2012 April 200 100 5/1/2012 2012 May 200 100 6/1/2012 2012 June 200 100 7/1/2012 2012 July 200 100 8/1/2012 2012 August 200 100 9/1/2012 2012 September 200 100 10/1/2012 2012 October 200 100 11/1/2012 2012 November 200 100 12/1/2012 2012 December 200 100 1/1/2013 2013 January 300 200 2/1/2013 2013 February 300 200 3/1/2013 2013 March 300 200 4/1/2013 2013 April 300 200 5/1/2013 2013 May 300 200 6/1/2013 2013 June 300 200 7/1/2013 2013 July 300 200 8/1/2013 2013 August 300 200 9/1/2013 2013 September 300 200 10/1/2013 2013 October 300 200 11/1/2013 2013 November 300 200 12/1/2013 2013 December 300 200 1/1/2014 2014 January 400 300 2/1/2014 2014 February 400 300 3/1/2014 2014 March 400 300 4/1/2014 2014 April 400 300 5/1/2014 2014 May 400 300 6/1/2014 2014 June 400 300 7/1/2014 2014 July 400 300 8/1/2014 2014 August 400 300 9/1/2014 2014 September 400 300 10/1/2014 2014 October 400 300
• ###### 5. Re: Lookup function to find the Last Value of a Previous partition?

This works, but its a bit messy:

IF attr([Month of Date])=="January"

then LOOKUP(SUM(F4),-1)

elseif attr([Month of Date])=="February"

then LOOKUP(SUM(F4),-2)

elseif attr([Month of Date])=="March"

then LOOKUP(SUM(F4),-3)

elseif attr([Month of Date])=="April"

then LOOKUP(SUM(F4),-4)

elseif attr([Month of Date])=="May"

then LOOKUP(SUM(F4),-5)

elseif attr([Month of Date])=="June"

then LOOKUP(SUM(F4),-6)

elseif attr([Month of Date])=="July"

then LOOKUP(SUM(F4),-7)

elseif attr([Month of Date])=="August"

then LOOKUP(SUM(F4),-8)

elseif attr([Month of Date])=="September"

then LOOKUP(SUM(F4),-9)

elseif attr([Month of Date])=="October"

then LOOKUP(SUM(F4),-10)

elseif attr([Month of Date])=="November"

then LOOKUP(SUM(F4),-11)

else LOOKUP(SUM(F4),-12)

end

Now I'm thinking there's probably a way to simplify that by using a single LOOKUP with a dynamic offset based on the value of the month.  I'll see if I can figure that out.

• ###### 6. Re: Lookup function to find the Last Value of a Previous partition?

Yep, here it is as a simpler, single LOOKUP calc, which uses the Month's integer value as the offset:

I used a Custom Date field to leverage the months as integer values -- that field is now in the Data Window/Field List.  Cheers

1 of 1 people found this helpful
• ###### 7. Re: Lookup function to find the Last Value of a Previous partition?

Awesome!!!  Thanks so much for your help.  Really appreciate it.

• ###### 8. Re: Lookup function to find the Last Value of a Previous partition?

Not a problem; it was good for me to go through that and figure out the dynamic method -- took some dot connecting in my own brain.

Be sure to mark your question as answered so others can find the solution easily down the road.  Cheers