Autogenerate Calendar script with custom month format

[#1]

Hello experts !

I have the following piece of code in my report for common calendar. I want to Autogenerate the calendar with the script which should create single digit months as 01 , 02 etc.,

NOTE: Min , Max dates can be set in variables (from Transaction table's Min and Max dates).

Calendar:

LOAD * INLINE [

    Cal_Year, Cal_Month, Cal_YearMonth

    2016, 01, 201601

    2016, 02, 201602

    2016, 03, 201603

    2016, 04, 201604

    2016, 05, 201605

    2016, 06, 201606

    2016, 07, 201607

    2016, 08, 201608

    2016, 09, 201609

    2016, 10, 201610

    2016, 11, 201611

    2016, 12, 201612

    2015, 01, 201501

    2015, 02, 201502

    2015, 03, 201503

    2015, 04, 201504

    2015, 05, 201505

    2015, 06, 201506

    2015, 07, 201507

    2015, 08, 201508

    2015, 09, 201509

    2015, 10, 201510

    2015, 11, 201511

    2015, 12, 201512

    2014, 01, 201401

    2014, 02, 201402

    2014, 03, 201403

    2014, 04, 201404

    2014, 05, 201405

    2014, 06, 201406

    2014, 07, 201407

    2014, 08, 201408

    2014, 09, 201409

    2014, 10, 201410

    2014, 11, 201411

    2014, 12, 201412

    2013, 01, 201301

    2013, 02, 201302

    2013, 03, 201303

    2013, 04, 201304

    2013, 05, 201305

    2013, 06, 201306

    2013, 07, 201307

    2013, 08, 201308

    2013, 09, 201309

    2013, 10, 201310

    2013, 11, 201311

    2013, 12, 201312

];

-JB

[marcus_sommer]

Your query is how to create YearMonth? For this you could use:

year(Date) * 100 + month(Date) as YearMonth

For other challenges to this topic see: How to use - Master-Calendar and Date-Values

- Marcus

[Gysbert_Wassenaar]

You can use the num function to format a number so it will display as double digits: num(MonthValue, '00')

[Anonymous]

Hi James,

Hope below mentioned code helps you.

Let vMin = Date('1/1/2016');

Let vMax = Date('5/5/2016');

TempCal:

LOAD

Date(Date#('$(vMin)')-1 + recno())  as Temp_Date

AUTOGENERATE (Date#('$(vMax)')-Date#('$(vMin)'))+1;

MasterCalendar:

LOAD Temp_Date as Date,

     Year(Temp_Date) as Cal_Year,

     num(Month(Temp_Date),'00') as Cal_Month,

     Year(Temp_Date)&num(Month(Temp_Date),'00')  as Cal_YearMonth    

Resident TempCal;

DROP Table TempCal;

[#5]

Getting below error ! I even created variable vMin vMax and assgined values

[Miguel_Angel_Baeyens]

Try using SET instead ot LET or using literal values like

SET vMin = '31/12/2016'; // or the date input mask you are using

[#7]

No Luck buddy

Date#('$(vMin)')-1 will this work correctly ? I don't have any doubt in the script other than this.

[Anonymous]

It works fine for me. PFA for the sample.

From error message it seems your variables are not getting initialized.