How to Assign Date Values based on a Current Month Key

Mo — Fri, 09 Jun 2023 18:01:49 GMT

I have data from a data base that assigns a column for the Current Month number when the data gets pulled, and then creates separate columns for previous months counting back. So for a 24 month data pull from September, there will be a column "Current Month" that has the value "9" for all rows, then it will have 24 columns with subscript header "_01", "_02", ..."_24", which denotes "Aug 2021", "Jul 2021",..."Sep 2019".

I used a manual formula to put in each month, but would like to automate this using the "Current Month" value. I have no idea how to do this, for both the month and the year, since year is changing based on what the value is for Current Month.

Any help would be appreciated very much!

This is a data table script for a 24 month row example with a manually generated DateKey column based on the Current Month value being September:

dt=New Table( "Set Month with Key Example",
	Add Rows( 24 ),
	Compress File When Saved( 1 ),
	New Column( "Month",
		Character,
		"Nominal",
		Set Values(
			{"01", "02", "03", "04", "05", "06", "07", "08", "09", "10", "11", "12",
			"13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24"}
		)
	),
	New Column( "DateKey",
		Character,
		Set Values(
			{"01082021", "01072021", "01062021", "01052021", "01042021", "01032021", "01022021", "01012021", "01122020", "01112020", "01102020", "01092020", "01082020", "01072020", "01062020", "01052020", "01042020", "01032020", "01022020", "01012020", "01122019", "01112019", "01102019", "01092019"}
		)
	)
); 

dt:DateKey << Data Type( Numeric, Format( "m/y", 8 ), Input Format( "ddmmyyyy" ) ) <<
Set Modeling Type( "Continuous" ) << Set Field Width( 12 );

CurMonth=9;

Monique

Re: How to Assign Date Values based on a Current Month Key

jthi — Fri, 08 Oct 2021 13:08:54 GMT

Here is one possible solution using Date Increment. Done as Formula but should be fairly easy to use in loop if you want to rename columns:

CurMonth = 9;
currentDate = Date DMY(1, curmonth, Year(Today()));
Date Increment(currentDate, "Month", -1 * Num(:Month), "start");

First curMonth is used to generate currentDate with the assumption that the start year is same as current year, then that date is used in Date Increment function.

Re: How to Assign Date Values based on a Current Month Key

Mo — Fri, 08 Oct 2021 13:19:40 GMT

Thanks so much, that works great!

topic Re: How to Assign Date Values based on a Current Month Key in Discussions

How to Assign Date Values based on a Current Month Key

Re: How to Assign Date Values based on a Current Month Key

Re: How to Assign Date Values based on a Current Month Key