What's the ideal way to create date calculation with filter?

[RL_Boston]

Hi,

I have relationships created for three tables based on Case#.

I am trying to create a new calculated date dimension that adds the [Days_Till_Expire] to the most current [Date] for whichever identified Case_id.

Table1: Log
Column: Date

Table2: Status
Column: Days_Till_Expire

Table3: Cases

Calculation Definition:
  CASE
  When ( [Cases].[Case_Id] = [Log].[Case_Id] )
  Then ( _add_days ( maximum (  [Log].[Date] ),  [Status].[Days_Till_Expire]  ) )
  End

However, I am not getting any results. Any suggestions would be much appreciated!
Thanks

[cognostechie]

Create another Query Subject with this kind of SQL:

Select
  Log#,
  max(Date) as 'MaxLogDate'
from Table1

Join this Query Subject to your original Table1 Query Subject with Case# and Log#. Make an outer join on original Table1 QS.

Create a Model Query subject and include items from Table1, Table2 and new QS and add a calculation:

If MaxLogDate is not null then _add_days(MaxLogDate,Days_Till_Expire)

Since there will be an outer join, the MaxLogDate will be null for records that are not most recent.

You can also try - If MaxLogDate <> Log.Date then _add_days(MaxLogDate,Days_Till_Expire)

This is a pueudo code, check the syntax.

[RL_Boston]

I will have to try this, much appreciated - Thanks  :)