How to assign consecutive numbers to months for a whole Date range

[pranaview]

Hi Guys,

Requirement: I want to create a field in my master calendar for the Date that i am using to filter on. This field must have numbers based on the months of the date in incremental order. 

For example: If vMin of the Date is 07/20/2017 and vMax is 08/31/2019 then the new field should have 1 value for July month of year 2017 and 2 for August of 2017 and so on till Aug of 2019. I hope i have made the requirement clear enough.

Any suggestion or tips will be appreciated as i need to get this done quickly.

Thanks!

Pranav

[Frank_Hartmann · Accepted solution]

maybe like this:

LET vDateMin = Num(MakeDate(2019,1,1));
LET vDateMax = Num(MakeDate(2020,1,1));
LET vDateToday = Num(Today());

TempCalendar:  
LOAD 
$(vDateMin) + RowNo() - 1 AS DateNumber,  
Date($(vDateMin) + RowNo() - 1) AS TempDate
AUTOGENERATE 1  
WHILE $(vDateMin)+IterNo()-1<= $(vDateMax);  

MasterCalendar:  
LOAD DISTINCT
(year(TempDate)*12 + Month(TempDate)) - (year($(vDateMin))*12 + Month($(vDateMin))) as MonthNumber,
(year(TempDate)*12 + Month(TempDate)) - (year($(vDateToday))*12 + Month($(vDateToday))) as MonthDiff,
Date(TempDate,'YYYY-MM') AS CalendarMonthAndYear
RESIDENT TempCalendar 
ORDER BY TempDate ASC;  
DROP TABLE TempCalendar;  

[Brett_Bleess]

Pranav, did Frank's post get you what you needed?  If so, please use the Accept as Solution button to mark his post, so others will know that it worked, and he gets credit for the help provided.  If you did something else, consider posting what you did and mark that, and if you are still working on things, leave an update on where things stand.

Regards,
Brett

[qliksus]

if you need just one date for each month they you can use addmonth like the below
let vCount = ((date('31/08/2019') - date('20/07/2017') )/365)*12
load
addmonths( vMin, recno()) as Date

autogenerate(vCount )
;

[pranaview · Accepted solution]

Hi Brett,

Apologies for the delayed response. Frank's solution actually worked but I also tried one thing and it also worked. So i simply added the below line of code in my already built Master calendar. The only difference is that Frank's solution starts the numbering from 0 where as mine does it from 1.

Autonumber(Date(MonthStart(Date),'MMM-YY')) As MonthNumber,

Would be nice if people can validate this one as i have just tested this one in my application. I'll tick the Frank's solution though as it is also correct.

Regards,
Pranav