Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Create YrMth filed in master calendar

Hello All,

I need help to create YrMth filed,  as attached screenshot.

MasterCalc.JPG

1 Solution

Accepted Solutions
ahaahaaha
Partner - Master
Partner - Master

Look attacahed file. I have not table "Orders". Therefore used


LET vMinDate = Date('01.01.2015');

LET vMaxDate = Date(Today());


So that the code matches the original pattern, I applied.

RecNo()-10 as MthCode

Here you can use just RecNo(). Then in the master calendar everything is as usual.

View solution in original post

3 Replies
ahaahaaha
Partner - Master
Partner - Master

Hi,

May be like this

LOAD

Date(AddMonths('01.11.2015', RecNo()-1), 'YYYYMM') as FiscalYrMht,

Date(AddMonths(AddMonths('01.11.2015', RecNo()-1), IterNo()-3), 'YYYYMM') as YrMht,

Month(AddMonths('01.11.2015', RecNo()-1)) as Mht,

RecNo() as MthCode

AutoGenerate 9

While IterNo()<4;


Example at attached file.

Regards,

Andrey

Anonymous
Not applicable
Author

Hi Andrey,

Thanks for your answer.

It working fine and i need some changes in this.  when i am using this script in master calendar i am not getting the correct values. Please look in the below script and do the needful. Thanks in advance.

MasterCalendar:

LOAD

Min(OrderDate)as MinDate,

Max(OrderDate) as MaxDate

RESIDENT Orders;

LET vMinDate = Peek('MinDate', 0, 'Temp_Calendar_Range');

LET vMaxDate = Peek('MaxDate', 0, 'Temp_Calendar_Range');

DROP TABLE Temp_Calendar_Range;

LOAD

Date(AddMonths($(vMinDate), RecNo()-1), 'YYYYMM') as FiscalYrMht1,

Date(AddMonths(AddMonths($(vMinDate), RecNo()-1), IterNo()-3), 'YYYYMM') as YrMht1,

Month(AddMonths($(vMinDate), RecNo()-1)) as Mht1,

RecNo() as MthCode1

AutoGenerate 36

While IterNo()<4;

Load    *,  

  AutoNumber(FiscalYear & Month& OrderDate, 'OrderDateId') as OrderId,  

  AutoNumber(FiscalYear & Quarter, 'QuarterID') as QtrCode,

  AutoNumber(FiscalYear & Month, 'MonthID') as MthOrder,

  Month(AddMonths(OrderDate,-2)) &' '& FiscalYear as FiscalMonth,

  'FY'&Right(FiscalYear,2)& Quarter as   FisQtr,

  Month(AddMonths(OrderDate,-2)) as Mth,

  Date(AddMonths(OrderDate,-2),'YYYYMM') as FiscalYrMth;

LOAD DISTINCT

Temp_Date as OrderDate,

Year(Temp_Date) as FiscalYear,

Month(Temp_Date) as [Month],

num(Month(Temp_Date)) as MthCode,

AutoNumber(year(Temp_Date)&(Month(Temp_Date))) as monthid,

Date(Temp_Date, 'YYYY-MM') as [Year - Month],

'Q' & Ceil(Month(Temp_Date) / 3) as [Quarter];

LOAD DISTINCT

MonthStart($(vMinDate) + IterNo() - 1) as Temp_Date

AUTOGENERATE (1)

WHILE $(vMinDate) + IterNo() - 1 <= $(vMaxDate);

ahaahaaha
Partner - Master
Partner - Master

Look attacahed file. I have not table "Orders". Therefore used


LET vMinDate = Date('01.01.2015');

LET vMaxDate = Date(Today());


So that the code matches the original pattern, I applied.

RecNo()-10 as MthCode

Here you can use just RecNo(). Then in the master calendar everything is as usual.