4 Replies Latest reply on Jan 28, 2013 2:53 AM by Prashant Sharma

# How to calculate latest records from particular date?

Hi,

I have to find only 50 records which are latest for Particular Date. As if i select 3/12/2012 as date then i want latest 50 data(top 50 nearer to this date) prior to this date. On these 50 records i am going to do some calculations.

• ###### 1. Re: How to calculate latest records from particular date?

Sharma,

One possible solution to this would be to configure your view to use 2 separate filters.

One filter gets rid of all records later than your chosen date and the second filter then selects the top 50 remaining records, based on their date.

To do get filter number 1, add the date to the filter shelf and select Range of Dates and then select Ending Date from the window and put in your relevant date. You can turn on Show quick filter for this so that you can quickly edit the date later.

For filter number 2, this is a bit more complex and involves using a custom calculation that works out the ranking of each record according to it's date. Then you can filter on this calculation so that only 1 - 50 are shown. The ranking calculation uses the INDEX() function

You can see more on this method here http://www.theinformationlab.co.uk/2012/10/01/using-index-to-filter-top-n-values-within-a-category-video/

I have attached a workbook with an example to help you understand the method.

• ###### 2. Re: How to calculate latest records from particular date?

Thanks Robin,

I used INDEX() but it shows me values in reverse order means from the starting date. Actually i have ending date fixed & want to show records which are latest by that ending date. Eg: if i choose 2/12/2012 as my date then i want records which are near to this date from previous order means data of 2/11/2012 comes first & then 2/10/2012 data will come.

• ###### 3. Re: How to calculate latest records from particular date?

I think you will need to change the way the INDEX function is computed -- set it to order along your date field in a descending order.

See the pic below...

Hope that helps

• ###### 4. Re: How to calculate latest records from particular date?

Thanks Robin,

It worked for me.