8 Replies Latest reply on Aug 24, 2017 12:13 AM by Patrick Myles

last time a value changed

Hi all

I'm trying to find a calculation to identity the last time a value changed based on dates.  For example, in the below image, the last date that the value changed was march 2017.  so i need a calculation that will find this date.

Thanks for any ideas!

Pat

• 1. Re: last time a value changed

Is this what you're after ?

• 2. Re: last time a value changed

Hi Patrick,

Try lookup(sum([value])-1). Appreciate if you could post a .twbx if you need assistance putting it together.

Alternately, you could look here: Lookup function to find the Last Value of a Previous partition?

HTH

Peter

• 3. Re: last time a value changed

Hi, Patrick

Please see my solution below

Hope this could help

ZZ

• 4. Re: last time a value changed

Hey Chris - not quite - i need to be able to return the date.  The ideas is that i can produce a list of values and then a column of 'last changed' dates as a column.  does make sense?

• 5. Re: last time a value changed

here is an example of what i mean ,  the intended output (on the right) is what i'm looking to produce based on the data on the left.

the 'last time the value changed' is the new field i need.  it should be calculated on chronological order too

• 6. Re: last time a value changed

hi ZZ - i'm not sure on your solution.  could you share the workbook?  also please see above with an example of what i want to achieve

• 7. Re: last time a value changed

Hi, Patrick

Please find attached workbook.

according to your sample data, I think you should add the name to the calculation as well.

{ FIXED [name]:MIN(

IF { FIXED [name], [Order Date]:AVG([Value])} = { FIXED [name] :AVG(IF [Order Date] = { FIXED [name]:MAX([Order Date])} THEN [Value] END)} THEN

[Order Date]

END

)

}

If it is still not working for you, would you be able to provide a sample data set so that easy for me providing the solution.

Hope this could help

ZZ

1 of 1 people found this helpful
• 8. Re: last time a value changed

thanks again ZZ your solution was perfect.  I find the calculation beyond my understanding but happy it works.  I have another post at calculate a medians on calculated values  just in case you might be able to help that one .

Thank you again for you kind support.