How to add YTD and MTD Fields?

[Anonymous]

I loaded a master calendar in my script below: I would also like to have a YTD and MTD Field added. Similar to how the Quarter is.

Quarter Field look on Dashboard ---->

I would like the same look of the "quarter" for year to date month to date , if possible

QuartersMap:
MAPPING LOAD
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
AutoGenerate (12);

Temp:
Load
min(DischargeDTS) as minDate,
max(DischargeDTS) as maxDate

Resident DischargeHeader;


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

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 DischargeDTS,
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,
If (Month(TempDate) = 12, Year(TempDate) + 1, Year(TempDate)) as FiscalYear,
Week(TempDate) & '-' & Year(TempDate) as WeekYear,
WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

• Reply • Like Show 0 Likes(0)

[trdandamudi · Accepted solution]

Follow the below link and hope this helps...

YTQ, QTD, MTD and WTD

[Anonymous]

This worked, thank you!

[trdandamudi]

You are welcome and glad I am able to assist.