Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
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
You can use
YeartoDate(TempDate, 4)*-1 as CurYTDFlag,
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