topic Re: Autogenerate Calendar script with custom month format in QlikView

Autogenerate Calendar script with custom month format

Tue, 29 Nov 2016 07:17:42 GMT

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

Re: Autogenerate Calendar script with custom month format

marcus_sommer — Tue, 29 Nov 2016 07:31:33 GMT

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

Re: Autogenerate Calendar script with custom month format

Gysbert_Wassenaar — Tue, 29 Nov 2016 07:41:22 GMT

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

Re: Autogenerate Calendar script with custom month format

Anonymous — Tue, 29 Nov 2016 07:50:18 GMT

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;

Re: Autogenerate Calendar script with custom month format

Tue, 29 Nov 2016 09:14:19 GMT

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

Re: Autogenerate Calendar script with custom month format

Miguel_Angel_Baeyens — Tue, 29 Nov 2016 09:39:46 GMT

Try using SET instead ot LET or using literal values like

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

Re: Autogenerate Calendar script with custom month format

Tue, 29 Nov 2016 09:45:38 GMT

No Luck buddy

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

Re: Autogenerate Calendar script with custom month format

Anonymous — Tue, 29 Nov 2016 10:17:09 GMT

It works fine for me. PFA for the sample.

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