12 Replies Latest reply on Oct 25, 2016 4:44 PM by Ivan Young

# How to pull value from the most recent date?

Hello everyone,

I need to have some sort of calculation that pulls the "Score" from the most recent date available.

Here is a sample of my table:

DateScore
1/1/201590%
2/1/201587%
3/1/201599%
4/1/201567%
5/1/201578%
6/1/201592%
7/1/2015

As you can see, 7/1/2015 has no data available, so the score for 6/1/2015 should be shown.

This is a job for TOTAL(MAX([DATE])), right? I'm not sure how to implement that though.

Can anyone assist?

• ###### 1. Re: How to pull value from the most recent date?

Hi Darren,

Find below my approach based on Lookup function

Lookup:

if IFNULL(LOOKUP(sum([Score]),Last()),0)=0 then LOOKUP(sum([Score]),last()-1)

else LOOKUP(sum([Score]),Last()) END

• ###### 2. Re: How to pull value from the most recent date?

Hi Norbert,

Thanks for the reply. So what I'd like to ultimately do is create a table consisting of these calculated fields:

 Overall AVG Score: 85.5% Number of Tests to Date: 6 Most Recent Test Score: 92%

Is this possible?

• ###### 3. Re: How to pull value from the most recent date?

An alternative solution is the following formula:

IF [Date] = { MAX(IF NOT ISNULL([Score]) THEN [Date] END)} THEN [Score] END

1 of 1 people found this helpful
• ###### 4. Re: How to pull value from the most recent date?

This is possible.

Just taking the AVG of Score gives you what you need for the overall avg.

This formula summed gives the number of tests to date: IF NOT ISNULL([Score]) THEN 1 END

The 92% is returned from the above formula: IF [Date] = { MAX(IF NOT ISNULL([Score]) THEN [Date] END)} THEN [Score] END

You would put your table together using Measure Names/Measure Values.

• ###### 5. Re: How to pull value from the most recent date?

Hi, thanks Andrew.

Worth mentioning that [score] is an aggregate calculated field.

When I enter your formula as you put it, I got an error "Cannot mix aggregate and non-aggregate comparisons or results in 'IF' expressions"

• ###### 6. Re: How to pull value from the most recent date?

Ok, that's an important bit of info. You can try altering the formula to:

IF ATTR([Date]) = { MAX(IF NOT ISNULL([Score]) THEN ATTR([Date]) END)} THEN [Score] END

I haven't tested this to confirm it definitely works.

• ###### 7. Re: How to pull value from the most recent date?

Thanks again Andrew.

I receive this error: Argument to MAX (an aggregate function) is already an aggregation and cannot be further aggregated.

• ###### 8. Re: How to pull value from the most recent date?

Hi Darren,

You should probably provide Andrew with a little more information about your data.  If score is an aggregate, then what you shared is probably not your table but rather a worksheet view of data aggregated to month.  This type of problem is generally easy to solve if the structure of the underlying data is know.

Regards,

Ivan

• ###### 9. Re: How to pull value from the most recent date?

As Ivan mentions this could be simple or could be very complex, seeing more of your data would help. It's late for me now, if someone else hasn't answered this overnight I'll try and take a look at your increased data set tomorrow.

• ###### 10. Re: How to pull value from the most recent date?

Hi Dareen,

Regards,

Tharashasank

• ###### 11. Re: How to pull value from the most recent date?

OK, here is my workbook.

• ###### 12. Re: How to pull value from the most recent date?

Hi Darren,

Thanks for posting the twbx, however the data in the workbook doesn't really correspond to your example.  Every month in the dataset has a score.

If you did have a month with no data you would use IFNULL and Previous_Value() to populate with the empty row with the prior months value.

If you wanted logic that will return the latest month and associated score you would need to create a custom date month/year and a calculated field { MAX([Auditdate (Month / Year)]) } = [Auditdate (Month / Year)] which you would drag to filters and set to True.

1 of 1 people found this helpful