I was wondering if anyone would be able to have a look at the script below and advise me how to change this so my Year Start date is August instead of the current January.
Your help would be really appreciated.
Regards
Adam
?
REM
Range:
LOAD
min(MyDate)
as
startdate,
max(MyDate)
as
enddate
resident
MyTable;
// DEFINE DATE RANGE HERE
Range:
LOAD
min
(%_dated) as startdate
,
max
(%_dated) as
enddate
resident
SalesInfo;
//Peek out the values for later use
let
vStart = peek('startdate',-1,'Range')-1
;
let
vEnd = peek('enddate',-1,'Range')
;
let
vRange = $(vEnd) - $(vStart)
;
//Remove Range table as no longer needed
Drop
table
Range;
//Generate a table with a row per date between the range above
Date:
Load
$(vStart)
+recno() as
Date
autogenerate
$(vRange)
;
qualify
* ;
unqualify
"%_*" ;
// unqualify all key fields
//Calculate the Parts you need to examine
SLCalendar:
load
Date
as %_dated
,
date
(Date,'dd/mm/yyyy') as FullDate
,
Year
(Date) as CalendarYear
,
'Q'
&ceil(Month(Date)/3) AS Quarter
,
// right(yearname(Date,0,$(vFiscalMonthStart)),4) as Cal_FiscalYear,
if(InYear (Date, today(), -1),1) as FULL_LastYr,
// All Dates Last Year
if(InYear (Date, today(), 0),1) as FULL_ThisYr,
// All Dates This Year
if(InYearToDate (Date, today(), 0),1) as YTD_ThisYr,
// All Dates to Date this Year
// YTD_TY, used in Expressions Ex. Sum(Sales*YTD_TY),
if(InYearToDate (Date, today(), -1),1) as YTD_LastYr,
// All Dates to Date Last Year
// YTD_LY, used in Expressions Ex. Sum(Sales*YTD_LY),
quartername(Date) as cal.CalendarQuarter
,
// quartername(Date,0,$(vFiscalMonthStart)) as Cal_FiscalQuarter,
// Month(Date)&'-'&right(yearname(Date,0,11),4) as Cal_FiscalMonthYear, //Fiscal!
Month(Date)&'-'&right(year(Date),4) as MonthYear
,
Month
(Date) as Month
,
Day
(Date) as Day
,
Week
(Date) as Week
,
Weekday
(Date) as
WeekDay
resident
Date;
//Tidy up
Drop
table
Date;
rename
tableSLCalendarto
SLCalendar ;
// unqualify * here, so that any following tabs will manage the qualify settings wholly within that tab.
