Author Topic: Getting the record to show only based on the maximum date  (Read 172 times)

Offline gosoccer

  • Statesman
  • ******
  • Join Date: Aug 2012
  • Posts: 642
  • Forum Citizenship: +1/-1
  • Be kind and help others. You'll face the same!
Hi everyone,
I have a unique situation. I'm creating a List in Cognos Analytics and populating three data elements.

ID       STATUS     DATE
1111       NEW     Dec 15, 2016 2:30 Pm
1111       SOLD    Dec 17, 2016 1:00 Pm

So what I need to show on the list is one entry as
1111       SOLD    Dec 17, 2016 1:00 Pm and not both entries.


Is there a way to use the Filter and MAXIMUM feature to show the data without programming in Framework Model to get only the most up-to-date.

When I drag to the list the three data elements, even after using the below Query Expression, I'm still getting both records.

"Go to the Toolbox tab, drag in a query calculation, and set the expression to be maximum([your date time field] for [your record id])"

Thank you for your time.




Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Getting the record to show only based on the maximum date
« Reply #1 on: 20 Feb 2018 12:46:24 pm »
Quote
set the expression to be maximum([your date time field] for [your record id])"

Try, as a Summary Filter, in your Query ...
[your date time field]=Maximum([your date time field] for [your record id])

HTH, Bob

Offline gosoccer

  • Statesman
  • ******
  • Join Date: Aug 2012
  • Posts: 642
  • Forum Citizenship: +1/-1
  • Be kind and help others. You'll face the same!
Re: Getting the record to show only based on the maximum date
« Reply #2 on: 20 Feb 2018 01:52:43 pm »
This approach worked perfectly Rob. Greatly appreciate it.  :) :)

Offline Cognos_Jan2017

  • Statesman
  • ******
  • Join Date: Jan 2017
  • Posts: 305
  • Forum Citizenship: +1/-0
Re: Getting the record to show only based on the maximum date
« Reply #3 on: 20 Feb 2018 02:03:56 pm »
Happy to help.

Thank you, Bob

 


       
Twittear