1

Say I have a Date/Time [Date] column and a numerical [Value] column. I am creating a calculated column. I need a formula that will find the row with the most recent date in the [Date] column, then return the data in its [Value] column. Is this possible in SharePoint 2007?

EDIT: My requirements (sorry for the long read)

I have 8 KPI measures that are updated monthly. Currently I'm storing these in a list, with the 8 measures as 8 columns. I also have a date column and a month column that is calculated from that date column. I need to display the latest record from this list in a KPI list (where I will set the boundaries for the indicator colours, etc.)

What I thought would solve it would be creating a 'Latest' view of the list that has the values for the measures for each month, and then limiting the number of entries returned to 1 item and sorting by date. This does indeed display only the latest record.

However, when I create an 'Indicator using data in SharePoint list', set the view to this new 'Latest' view, and go 'Calculation using all list items in the view' -> Sum, it sums over all the items, instead of the ones in the 'Latest' view.

Is my only option likely to be using custom code, or can my current method be fixed somehow?

|improve this question
3

No a calculated column can only look at the data in the current row, it can not retrieve information from other rows to produce the result you are mentioning.

You will need to achieve this result through custom code. This can be either server side or client side code with javascript to set the values of a text field based on this calculation.

|improve this answer
  • 1
    Thank you. Is it also not possible to create a list view that shows only the row with the most recent date in the [Date] column? edit: I think I have it, I just need to limit the number of items returned. This should solve my problem. – false_azure Feb 13 '14 at 23:28
  • Yes you can do exactly that, create a list view sorted by date and only return 1 item. If you want a less interactive page (list view will still have filtering options and ribbon commands) you could use a content query web part to query the list and only return a single item, and for that item to be the most recent. – user23429 Feb 13 '14 at 23:39
  • If you are having any difficulty, you can post your requirements here and we can help you with various ideas on how to deliver this – user23429 Feb 13 '14 at 23:39
  • I think some of what you currently have in your solution is a bit unclear to me. My understanding is that you have a list of items and you only want to base your indicator values on the latest one. You are then creating an indicator list (which you referred to as a KPI list, just making sure we are on the same page) to display the indicator based on the various fields in the latest item – user23429 Feb 14 '14 at 0:14
  • If my understanding from above is accurate, then I don't think you will get the result you are looking for using an indicator list. The indicator list is meant for calculating based on all list items, or showing an indicator based on the maxiumum or minimum value of a particular field, but this will not take into account other fields – user23429 Feb 14 '14 at 0:17

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.