Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
You can use the num function to format a number so it will display as double digits: num(MonthValue, '00')
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;
Getting below error ! I even created variable vMin vMax and assgined values
Try using SET instead ot LET or using literal values like
SET vMin = '31/12/2016'; // or the date input mask you are using
No Luck buddy
Date#('$(vMin)')-1 will this work correctly ? I don't have any doubt in the script other than this.
It works fine for me. PFA for the sample.
From error message it seems your variables are not getting initialized.