Writing SubQuery logic in Set Analysis

[#1]

I have a scenario as below,   

There is a fact A_FACT and is having fields like New_Joinee_Flag, Offer_Flag, Candidate_ID, Month_SID etc. the requirement is to find New_Joinee_Count for selected month and next month where the Candidate is having a offer in selected month.

below is the respective query used,

select sum(NEW_JOINEE_FLAG) from hcm_A_FACT where month_sid in( 201104,201105) and  CANDIDATE_SID in (

select CANDIDATE_SID from A_FACT where offer_flag =1 and month_sid = 201104)

since this needs to be dynamic can't write it in script level.

Anyone please help me in writing the same as set analysis expression.

[#2]

hI,

i think that your query must be something like this when u select month SID 201105

sum({$<CANDIDATE_SID =P({$<month_sid ={$(#=only(month_sid )-1)},offer_flag={1} >}CANDIDATE_SID )>}NEW_JOINEE_FLAG)

This is an example, if u attach a file with some example data is better.

But i hope this help u

C u,

Stefano