Filter by Most recent (latest) date

[mferguson]

I am creating a tabular summary report. The data source has list of dates with some other data fields and a corresponding ID number. I would like the report to just display one record for each ID number - the one with the most recent date - but the method to do this escapes me.

There are a lot of different ways to filter data (i.e. http://howto.caspio.com/datapages/creat ... types.html) but a 'maximum' or 'latest' criteria does not appear to be in there in terms of filtering either in reports or views.

I thought of using a maximum aggregation within the data display, but all that does is gives me the latest date and does not pull the other fields for that specific record.

Anyone else ever tried to do this or have any thoughts on an approach?

[Aariel]

hello,

1) go to 'Search and Report Wizard - Search Results Options'.

In the Standard tab,

2) Sort order: by the date field and do it descending order (i.e Z to A).

3) Records per page: 1

In the Advanced tab,

4) Record Options-> Total records returned: 1

Hope this helps.

[mferguson]

While that does give me one record with the most recent date, I unfortunately need one record with the most recent date for each id number present in my source table.

[londoncity]

Hello mferguson,

Let me see if I get your question correctly say you have a report like this which is grouped by IDs:

ID Date

55 12/12/2010

10/12/2010

08/12/2010

56 08/12/2011

07/12/2011

06/12/2011

and now you wish to see this report like below:

ID Date

55 12/12/2010

56 08/12/2011

Well, if that's the case then it can not be done in one DataPage. You need to create 2 DataPages.

On the first one you can display IDs and make them linkable, then when user click the link, it passes the ID as a parameter to the 2nd DataPage which is sorted the way described in the previous post by Aariel.

Please let me know if that's the scenario you were looking for. :mrgreen:

[mferguson]

Yes, LondonCity, that is exactly the scenario for what I am trying to accomplish. I'll give it a try with the two datapages and see if I can get it to work.

[Kurumi]

Hi @mferguson,

You may also refer to this forum post. On your Tabular Report, add a Calculated Field and modify the formula below: 

 

[GoodBoy]

Hello. This solution might work for you!