Filter item codes whose last transaction date was prior to 01-01-2015

[cognovice]

Hi,
I need to filter all item codes whose last transaction (based on tran_date) was prior to 01-01-2015.  There are huge numbers of transactions in this table but I only need to get the output for the last transaction that has occurred on the item code which is prior to 1st of Jan 2015.  Could anyone please advise how to achieve this.

[hespora]

...assuming this is relational...

- create a data item "maxdate":

Code Select Expand


maximum(
  [tran_date]
  for [itemcode]
)

- filter on "[maxdate] < 2015-01-01", set this filter to "after auto aggregation"
- filter on "[maxdate] = [tran_date]", set this to "after auto aggregation" as well

this will not necessarily yield you the last transaction, but instead all transactions per item that happened on the last transaction date for that material. if that's not what you want, then you'll need to introduce some other field to the mix, but this is what can be done via the date.