Look up maximum date

[#1]

I have a transaction table where each transaction has a date: TRAN_DATE

This table is linked to a date look up table which gives me our company time period that a date belongs to; this table has fields TRAN_DATE and TIME_PERIOD

The lookup table includes dates into the future so when I use the expression max(TRAN_DATE) I get the latest date in the look up table. I really want the maximum date in the transaction table.

Any pointers?

[#2]

I really want something like Max(TRAN_DATE where the TRANSACTION_ID is NOT NULL)!!

[vinieme12 · Accepted solution]

try

Max({<TRANSACTION_ID ={"=len(TRANSACTION_ID )>0"}>}TOTAL TRAN_DATE)

[jonathandienst]

With If statement:

=Max(If(Len(TRANSACTION_ID) > 0, TRAN_DATE ))

Set expresssion:

=Max({<TRANSACTION_ID = {'*'}>} TRAN_DATE)