Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2022, LIVE in Denver CO., May 16-19, 2022. REGISTER NOW TO RECEIVE EARLY BIRD PRICING
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Master Calendar Help

Hi,

I'm currently using the following script to create a Master Calendar within my Dashboard:

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

               min(actual_date) as minDate,

               max(actual_date) as maxDate

Resident MyTable;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

let vYesterday = Date(varMaxDate);

DROP Table Temp;

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

               TempDate AS CALL_DATETIME,

               week(TempDate) As Week,

               Year(TempDate) As Year,

               Month(TempDate) As Month,

               Day(TempDate) As Day,

               YeartoDate(TempDate)*-1 as CurYTDFlag,

               YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

               date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

               ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

               WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Is it possible to set the CurYTDFlag so that it runs from April to March?

Also, can the Quarters be reset so the run Q1 = Apr-May-Jun, Q2 = Jul-Aug-Sep, Q3 = Oct-Nov-Dec, Q4 = Jan-Feb-Mar?

Also, can anyone help on setting a Previous 30 Days Flag, a Previous 90 Days Flag a Previous 180 Days Flag?

Any help and advice greatly appreciated!

Many thanks,

Tom

1 Solution

Accepted Solutions
jonathandienst
Partner
Partner

Hi

I have adapted some code from my Calendar QVD generator that does what you are looking for:

//------------------------------------------------------------------------------------------

// Fiscal year parameters

//------------------------------------------------------------------------------------------

Set zGLYearStartMonth = 4; // Financial year start month (1 = same As cal year start)

Set zLastDOM = 31; // Last day of financial month (31 = end of cal month)

Let zCurrentYear = Year(Today(0));

Let vCurrentGLYear = zCurrentYear + If (zGLYearStartMonth = 1 Or Month(Today()) < zGLYearStartMonth, 0, 1);

  ..

  ..

//------------------------------------------------------------------------------------------

// Load Master Calendar

//------------------------------------------------------------------------------------------

MasterCalendar:

Load *,

  // Fiscal year fields - Fiscal Quarter

  Dual('Q' & Ceil(GLPeriodM / 4), Ceil(GLPeriodM / 4)) As GLQuarter,

  // Current fiscal year and last fiscal year flags

  If(GLYearOffset = 0, 1, 0) As CurYTDFlag,

  If(GLYearOffset = -1, 1, 0) As LastYTDFlag

;

Load *,

  // Fiscal year fields - Fiscal Year

  Year + If($(zGLYearStartMonth) <= 1 Or Month < $(zGLYearStartMonth), 0, 1) As GLYear,

  // Fiscal year fields - Fiscal Period

  If (Month >= $(zGLYearStartMonth),

  If(Day > $(zLastDOM), Month - $(zGLYearStartMonth) + 2, Month - $(zGLYearStartMonth) + 1),

  If(Day > $(zLastDOM), Month + (12 - $(zGLYearStartMonth) + 2), Month + (12 - $(zGLYearStartMonth) + 1))) As GLPeriodM,

  // Fiscal year fields - GLYearOffset

  If (CalMonth < $(zGLYearStartMonth),

  CalYear,

  CalYear + 1) - $(vCurrentGLYear) As GLYearOffset,

  // Aging flags

  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 30, 1, 0) As [Previous 30 Days Flag],

  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 90, 1, 0) As [Previous 90 Days Flag],

  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 180, 1, 0) As [Previous 180 Days Flag]

;

Load

  TempDate AS CALL_DATETIME,

  week(TempDate) As Week,

  Year(TempDate) As Year,

  Month(TempDate) As Month,

  Day(TempDate) As Day,

  //YeartoDate(TempDate)*-1 as CurYTDFlag,

  //YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

  inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

  date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

  WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

The variables are parameters that set the fiscal periods.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

3 Replies
jonathandienst
Partner
Partner

Hi

I have adapted some code from my Calendar QVD generator that does what you are looking for:

//------------------------------------------------------------------------------------------

// Fiscal year parameters

//------------------------------------------------------------------------------------------

Set zGLYearStartMonth = 4; // Financial year start month (1 = same As cal year start)

Set zLastDOM = 31; // Last day of financial month (31 = end of cal month)

Let zCurrentYear = Year(Today(0));

Let vCurrentGLYear = zCurrentYear + If (zGLYearStartMonth = 1 Or Month(Today()) < zGLYearStartMonth, 0, 1);

  ..

  ..

//------------------------------------------------------------------------------------------

// Load Master Calendar

//------------------------------------------------------------------------------------------

MasterCalendar:

Load *,

  // Fiscal year fields - Fiscal Quarter

  Dual('Q' & Ceil(GLPeriodM / 4), Ceil(GLPeriodM / 4)) As GLQuarter,

  // Current fiscal year and last fiscal year flags

  If(GLYearOffset = 0, 1, 0) As CurYTDFlag,

  If(GLYearOffset = -1, 1, 0) As LastYTDFlag

;

Load *,

  // Fiscal year fields - Fiscal Year

  Year + If($(zGLYearStartMonth) <= 1 Or Month < $(zGLYearStartMonth), 0, 1) As GLYear,

  // Fiscal year fields - Fiscal Period

  If (Month >= $(zGLYearStartMonth),

  If(Day > $(zLastDOM), Month - $(zGLYearStartMonth) + 2, Month - $(zGLYearStartMonth) + 1),

  If(Day > $(zLastDOM), Month + (12 - $(zGLYearStartMonth) + 2), Month + (12 - $(zGLYearStartMonth) + 1))) As GLPeriodM,

  // Fiscal year fields - GLYearOffset

  If (CalMonth < $(zGLYearStartMonth),

  CalYear,

  CalYear + 1) - $(vCurrentGLYear) As GLYearOffset,

  // Aging flags

  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 30, 1, 0) As [Previous 30 Days Flag],

  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 90, 1, 0) As [Previous 90 Days Flag],

  If(Today() - CALL_DATETIME >=0 And Today() - CALL_DATETIME <= 180, 1, 0) As [Previous 180 Days Flag]

;

Load

  TempDate AS CALL_DATETIME,

  week(TempDate) As Week,

  Year(TempDate) As Year,

  Month(TempDate) As Month,

  Day(TempDate) As Day,

  //YeartoDate(TempDate)*-1 as CurYTDFlag,

  //YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

  inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

  date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

  ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

  Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

  WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

The variables are parameters that set the fiscal periods.

HTH

Jonathan

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein

View solution in original post

yduval75
Partner
Partner

You can use

YeartoDate(TempDate, 4)*-1 as CurYTDFlag,

its_anandrjs

Hi,

For CurrentYTD Flag you can change like

   YeartoDate(AddMonths(TempDate,-3))*-1 as CurYTDFlag,

And for Quarter start from the Apr

ApplyMap('QuartersMap', month(AddMonths(TempDate,-3)), Null()) as Quarter,

Regards

Anand