Qlik Community

Ask a Question

App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Announcements
April 22, 2PM EST: Learn about GeoOperations in Qlik Sense SaaS READ MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
carlcimino
Creator II
Creator II

Earn-in Scenarios

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 CategoryEarn StartEarn FinishAnnualized Amount
Monthly03/01/202112/31/20225000
Immediate03/01/202112/31/20225000

 

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 CategoryEarn StartEarn Finishyear periodAnnualized AmountMonthly Amount
Monthly03/01/202112/31/20222021035000416.6667
Monthly03/01/202112/31/20222021045000416.6667
Monthly03/01/202112/31/20222021055000416.6667
Monthly03/01/202112/31/20222021065000416.6667
Monthly03/01/202112/31/20222021075000416.6667
Monthly03/01/202112/31/20222021085000416.6667
Monthly03/01/202112/31/20222021095000416.6667
Monthly03/01/202112/31/20222021105000416.6667
Monthly03/01/202112/31/20222021115000416.6667
Monthly03/01/202112/31/20222021125000416.6667
Monthly03/01/202112/31/20222022015000416.6667
Monthly03/01/202112/31/20222022025000416.6667
Monthly03/01/202112/31/20222022035000416.6667
Monthly03/01/202112/31/20222022045000416.6667
Monthly03/01/202112/31/20222022055000416.6667
Monthly03/01/202112/31/20222022065000416.6667
Monthly03/01/202112/31/20222022075000416.6667
Monthly03/01/202112/31/20222022085000416.6667
Monthly03/01/202112/31/20222022095000416.6667
Monthly03/01/202112/31/20222022105000416.6667
Monthly03/01/202112/31/20222022115000416.6667
Monthly03/01/202112/31/20222022125000416.6667
Immediate03/01/202112/31/202220210350005000
Immediate03/01/202112/31/20222022015000416.6667
Immediate03/01/202112/31/20222022025000416.6667
Immediate03/01/202112/31/20222022035000416.6667
Immediate03/01/202112/31/20222022045000416.6667
Immediate03/01/202112/31/20222022055000416.6667
Immediate03/01/202112/31/20222022065000416.6667
Immediate03/01/202112/31/20222022075000416.6667
Immediate03/01/202112/31/20222022085000416.6667
Immediate03/01/202112/31/20222022095000416.6667
Immediate03/01/202112/31/20222022105000416.6667
Immediate03/01/202112/31/20222022115000416.6667
Immediate03/01/202112/31/20222022125000416.6667
Labels (3)
1 Solution

Accepted Solutions
GaryGiles
Specialist
Specialist

@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'
;

View solution in original post

4 Replies
GaryGiles
Specialist
Specialist

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
Creator II
Creator II
Author

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
Specialist
Specialist

@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'
;

View solution in original post

carlcimino
Creator II
Creator II
Author

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