Earn-in Scenarios

[carlcimino]

Hello I have a set of data that has annualized amounts in a column with a start date and end date as well a a earn-in scenario.  What I have been asked is to be able to see the monthly earn in between the start and end dates.

Earned-In Category	Earn Start	Earn Finish	Annualized Amount
Monthly	03/01/2021	12/31/2022	5000
Immediate	03/01/2021	12/31/2022	5000

For the Monthly results what happens is every month including the start/finish months gets the amount /12 inserted.  What I have done successfully is this:

FACT_TEMP_NEW:
LOAD
  RecNo() as ID
, "Earned-In Category"
, "Earn Start" //earnings start date
, "Earn Finish" //end date for earnings calculation
, "Annualized Amount" //Amount to be earned out until Earnings End Date
, ("Annualized Amount"/12) as "Monthly Amount"
, AddMonths("Earn Start",IterNo()-1) as "Year Period"
FROM [lib://Finance/QVD/FACT.qvd]
(qvd)
While AddMonths("Earn Start",IterNo()-1)<"Earn Finish";
;

For the Earned-In Category = Immediate the requirement is that the total annualized amount gets inserted in the Year Period of the Earn Start and then in the following year it should behave the same as the Monthly.  Here is what the result should be for both Monthly and Immediate Earn-in Category.  Any ideas on how to accomplish the Immediate part would be much appreciated.

Earned-In Category	Earn Start	Earn Finish	year period	Annualized Amount	Monthly Amount
Monthly	03/01/2021	12/31/2022	202103	5000	416.6667
Monthly	03/01/2021	12/31/2022	202104	5000	416.6667
Monthly	03/01/2021	12/31/2022	202105	5000	416.6667
Monthly	03/01/2021	12/31/2022	202106	5000	416.6667
Monthly	03/01/2021	12/31/2022	202107	5000	416.6667
Monthly	03/01/2021	12/31/2022	202108	5000	416.6667
Monthly	03/01/2021	12/31/2022	202109	5000	416.6667
Monthly	03/01/2021	12/31/2022	202110	5000	416.6667
Monthly	03/01/2021	12/31/2022	202111	5000	416.6667
Monthly	03/01/2021	12/31/2022	202112	5000	416.6667
Monthly	03/01/2021	12/31/2022	202201	5000	416.6667
Monthly	03/01/2021	12/31/2022	202202	5000	416.6667
Monthly	03/01/2021	12/31/2022	202203	5000	416.6667
Monthly	03/01/2021	12/31/2022	202204	5000	416.6667
Monthly	03/01/2021	12/31/2022	202205	5000	416.6667
Monthly	03/01/2021	12/31/2022	202206	5000	416.6667
Monthly	03/01/2021	12/31/2022	202207	5000	416.6667
Monthly	03/01/2021	12/31/2022	202208	5000	416.6667
Monthly	03/01/2021	12/31/2022	202209	5000	416.6667
Monthly	03/01/2021	12/31/2022	202210	5000	416.6667
Monthly	03/01/2021	12/31/2022	202211	5000	416.6667
Monthly	03/01/2021	12/31/2022	202212	5000	416.6667
Immediate	03/01/2021	12/31/2022	202103	5000	5000
Immediate	03/01/2021	12/31/2022	202201	5000	416.6667
Immediate	03/01/2021	12/31/2022	202202	5000	416.6667
Immediate	03/01/2021	12/31/2022	202203	5000	416.6667
Immediate	03/01/2021	12/31/2022	202204	5000	416.6667
Immediate	03/01/2021	12/31/2022	202205	5000	416.6667
Immediate	03/01/2021	12/31/2022	202206	5000	416.6667
Immediate	03/01/2021	12/31/2022	202207	5000	416.6667
Immediate	03/01/2021	12/31/2022	202208	5000	416.6667
Immediate	03/01/2021	12/31/2022	202209	5000	416.6667
Immediate	03/01/2021	12/31/2022	202210	5000	416.6667
Immediate	03/01/2021	12/31/2022	202211	5000	416.6667
Immediate	03/01/2021	12/31/2022	202212	5000	416.6667

[GaryGiles]

Try this:

FACT_TEMP_NEW:
LOAD
RecNo() as ID
, "Earned-In Category"
, "Earn Start" //earnings start date
, "Earn Finish" //end date for earnings calculation
, "Annualized Amount" //Amount to be earned out until Earnings End Date
, ("Annualized Amount"/12) as "Monthly Amount"
, AddMonths("Earn Start",IterNo()-1) as "Year Period"
FROM [lib://Finance/QVD/FACT.qvd]
(qvd)
While AddMonths("Earn Start",IterNo()-1)<"Earn Finish"
and "Earned-In Category='Monthly';
;

//Load the start month record
LOAD
RecNo() as ID
, "Earned-In Category"
, "Earn Start"
, "Earn Finish"
, "Annualized Amount"
, "Annualized Amount" as "Monthly Amount"
, "Earn Start" as "Year Period"
FROM [lib://Finance/QVD/FACT.qvd]
(qvd)
While "Earned-In Category='Immediate';
;

//Load monthly records starting with months after Earn Start Year End
LOAD
RecNo() as ID
, "Earned-In Category"
, "Earn Start" //earnings start date
, "Earn Finish" //end date for earnings calculation
, "Annualized Amount" //Amount to be earned out until Earnings End Date
, ("Annualized Amount"/12) as "Monthly Amount"
, AddMonths("Earn Start",IterNo()-1) as "Year Period"
FROM [lib://Finance/QVD/FACT.qvd]
(qvd)
While AddMonths("Earn Start",IterNo()-1)<"Earn Finish"
and AddMonths("Earn Start",IterNo()-1)>YearEnd("Earn Start")
and "Earned-In Category='Immediate';
;

[carlcimino]

Hi @GaryGiles , Thank you very much for the suggestion.  Makes a lot of sense to break it down into pieces.  The first two parts work as expected.  I loaded the two example rows inline to test.  The 3rd part is not returning a result.  I think it must have to do with the while statement?  Any additional ideas?  

FACT_TEMP:
load * inline [
Earned-In Category,Earn Start,Earn Finish,Annualized Amount
Monthly,3/1/2021,12/31/2022,5000
Immediate,3/1/2021,12/31/2022,5000
]
;

FACT_TEMP_NEW:
LOAD
RecNo() as ID
, "Earned-In Category"
, "Earn Start" //earnings start date
, "Earn Finish" //end date for earnings calculation
, "Annualized Amount" //Amount to be earned out until Earnings End Date
, ("Annualized Amount"/12) as "Monthly Amount"
, AddMonths("Earn Start",IterNo()-1) as "Year Period"
resident FACT_TEMP
While AddMonths("Earn Start",IterNo()-1)<"Earn Finish"
and "Earned-In Category"='Monthly'
;

//Load the start month record
LOAD
RecNo() as ID
, "Earned-In Category"
, "Earn Start"
, "Earn Finish"
, "Annualized Amount"
, "Annualized Amount" as "Monthly Amount"
, "Earn Start" as "Year Period"
resident FACT_TEMP
where "Earned-In Category"='Immediate';
;

//Load monthly records starting with months after Earn Start Year End
LOAD
RecNo() as ID
, "Earned-In Category"
, "Earn Start" //earnings start date
, "Earn Finish" //end date for earnings calculation
, "Annualized Amount" //Amount to be earned out until Earnings End Date
, ("Annualized Amount"/12) as "Monthly Amount"
, AddMonths("Earn Start",IterNo()-1) as "Year Period"
resident FACT_TEMP
While AddMonths("Earn Start",IterNo()-1)<"Earn Finish"
and AddMonths("Earn Start",IterNo()-1)>YearEnd("Earn Start")
and "Earned-In Category"='Immediate'
;

drop table FACT_TEMP;

[GaryGiles · Accepted solution]

@carlcimino 

You are right, for the 3rd Load statement, we need to take a slightly different approach.  Try this:

LOAD
RecNo() as ID
, "Earned-In Category"
, "Earn Start" //earnings start date
, "Earn Finish" //end date for earnings calculation
, "Annualized Amount" //Amount to be earned out until Earnings End Date
, ("Annualized Amount"/12) as "Monthly Amount"
, AddMonths(YearEnd("Earn Start"),IterNo()) as "Year Period"
resident FACT_TEMP
While IterNo() <= ((year("Earn Finish")*12)+month("Earn Finish")) - (((year(YearEnd("Earn Start"))*12)+month(YearEnd("Earn Start"))))
and "Earned-In Category"='Immediate'
;

[carlcimino]

@GaryGiles This returned what was expected!  I have accepted your response as a solution.  Thank you very much for your help.