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
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.
I will have to try this, much appreciated - Thanks :)