Print Page - How to find previous maximum date

Title: How to find previous maximum date
Post by: shrini on 29 Aug 2014 11:19:01 AM

For example:-

Policy No   Date
1   10/12/2013
1   1/8/2012
1   23/06/2013

For policy no1 result would be 23/06/2013. I dont have any transaction number for each date to pick. Please let me know, if anybody has solution.

Thanks,
Srini.

Title: Re: How to find previous maximum date
Post by: MDXpressor on 29 Aug 2014 11:24:45 AM

A couple of ways to manage this shrini.

but with some brute force:

[minDate]:

Code Select

minimum([Namespace].[Query Subject].[Date] for [Policy No])

Then, filter for:

Code Select

[Namespace].[Query Subject].[Date]=[minDate]

This will leave you with only the 1st record.

If you want all records to show up, allow a rank on dates per Policy No to become your ID's:

Code Select

rank([Date] for [Policy No])

Title: Re: How to find previous maximum date
Post by: shrini on 01 Sep 2014 02:07:34 AM

Thanks MDX, I got it.

COGNOiSe.com - The IBM Cognos Community

IBM Cognos 10 Platform => Cognos 10 BI => Report Studio => Topic started by: shrini on 29 Aug 2014 11:19:01 AM