How to use Left join inside set analysis

[Jobson_joseph]

Hi,
Following is the requirement to create a custom/derived dimension, that will be used as a filter in the app in a drilldown table;

isnull([WS Status].[Latest WS Status],'Yet To Start') [WS Status]
(
distinct transaction.id
where system.eventType'= 'WorkViewServiceExecuted'
and system.status = 'Completed'
and eventContext.method  = 'updateOrReassignWi'
and eventContext.action = 'moveToHitl'
and system.timestamp <= end of reporting period
except
transaction.id
where system.eventType = 'CollaborationStarted'
and system.status = 'Completed'
and system.timestamp  < start of reporting period
) [Reached WS]
left join
(
transaction.id
,system.status [Latest WS Status]
where system.eventType = 'CollaborationStarted'
and system.timestamp') =max(system.timestamp)
)[WS Status]
on [Reached WS].[Submission ID] = [WS Status].[Submission ID]

I've come up with one set expresion, but it seems to be not working.

=if(IsNull($(vWS_Status)),'Yet To Start', $(vWS_Status))


where $(vWS_Status) variable is

Aggr(only ({<eventType={'a'},status={'Completed'},method={'method1'},action={'action1'},Date={"<=$(=Date(Max(Date),'MM/DD/YYYY'))"},
TX_ID=E({1<eventType={'b'},status={'Completed'},Date={"<$(=Date(Min(Date),'MM/DD/YYYY'))"}>} TX_ID) >
+
<eventType={'b'}, Date_Time={"$(=Date(Max(Date_Time),'MM/DD/YYYY hh:mm:ss'))"}>} status),TX_ID)

[Jobson_joseph]

Any response or ideas appreciated guys

[Kushal_Chawda]

@Jobson_joseph  What you have is query which will be difficult to interpret it as set expression. Possibly you can create some sample data in excel to explain the logic and what you want to achieve to get the best answers