Uses section access to specify a date range.
DateField would be whatever date field you have in your data. Could be [Accounting Date], [Billed Date] or whatever. But it would have to be a date field. If you don't have one, you should probably create one in your script.
If your data is at the month level only, add a date field where the date is the first day of the month.
The set analysis is saying pull the data from the beginning of the calendar year through the end of the available month derived from your date field.
I loaded a calendar in my script below: Now I am thinking what might be best is to have a Month to Date Field added. Similar to how the Quarter is.
I would like the same look of the "quarter" for month to day, if possible
rowno() as Month,
'Q' & Ceil (rowno()/3) as Quarter
min(DischargeDTS) as minDate,
max(DischargeDTS) as maxDate
Let varMinDate = Num(Peek('minDate', 0, 'Temp'));
Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));
DROP Table Temp;
$(varMinDate) + Iterno()-1 As Num,
Date($(varMinDate) + IterNo() - 1) as TempDate
AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
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
Order By TempDate ASC;
Drop Table TempCalendar;
I thought you didn't have a date field in your data? What does your calendar link to?
There's nothing wrong with a calendar, but I would normally make it to the level that your data is at.
So if you only have months, make your calendar's most detailed level, Month. That's my opinion.
to make your month and year field a date field with the first day of the month your would do something like
date(makedate([YourYearField],[YourMonthField],1),'M/D/YYYY') as MonthDate