Qlik Subroutine - Fiscal Year Fiscal Period Loop

Related to Qlik Subroutine Category - Fiscal Periods, see Qlik Subroutine - Recent Fiscal Period to help setup arguments for this table creation

SUB FY_FP_Loop(mFP_Start,mFP_End)
 
	/*
		Creates a temporary table named $(vTempTableName) specifically or looping within a Fiscal Period Loop
 
		Uses the 'AutoGenerate()' method to create a table from mFP_Start to mFP_End (Inclusive)
 
		Assumes: 
		- mFP_Start and mFP_End are a string in 'YYYYMM' format
 
		Args:
		- mFP_Start - Start date for table
		- mFP_End - End date for table
 
		Returns:
		- rFP_NoOfRows (int) - Number of records, i.e., number of Fiscal periods
 
		Example:
		- mFP_Start = '202401'
		- mFP_End = '202406'
 
		CALL FY_FP_Loop('202401','202406');
 
		// Table in Memory after CALL
		zFP_Loop:
		FP_Loop
		202401
		202402
		202403
		202404
		202405
		202406
	*/
 
	LET vTempTableName = 'zFP_Loop';
 
	IF NOT ISNULL(TableNumber('$(zFP_Loop)')) THEN
		DROP TABLE $(zFP_Loop);
	END IF
 
	// Dates are kept in a human readable format, YYYYMM, convert to a number for AutoGenerate()
	LET vMinDate = NUM(DATE#('$(mFP_Start)','YYYYMM'));
	TRACE vMinDate = $(vMinDate);
	LET vMaxDate = NUM(DATE#('$(mFP_End)','YYYYMM'));
	TRACE vMaxDate = $(vMaxDate);
 
	vTempTableName:
	LOAD
		IF(
			// First Row? Always be the minimum Date
			RowNo()=1, DATE($(vMinDate),'YYYYMM')
			// Any other Row? Add RowNo() - 1 months to the minimum date
				// e.g. Second iteration, need to add a single month to the minimum date, so 2 - 1, +1 month
			DATE(AddMonths($(vMinDate),RowNo() - 1),'YYYYMM')
		) AS FP_Loop
	AutoGenerate(1)
	WHILE AddMonths($(vMinDate),RowNo()) <= $(vMaxDate);
 
	LET rFP_NoOfRows('$(vTempTableName)');
 
END SUB;