Let varMinDate = Num(vStartdate); Let varMaxDate = Num(vEnddate);
NoConcatenate TempCalendar: LOAD $(varMinDate) + Iterno()-1 As Num, Date($(varMinDate) + IterNo() - 1) as TempDate, Monthname($(varMinDate) + IterNo() - 1) as Reporting_Month AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);
NoConcatenate
monthcalendar:
Load distinct Reporting_Month, MonthStart(TempDate) as monthstart, Monthend(TempDate) as monthend
Resident TempCalendar;
drop table TempCalendar;
The output shows Reporting month, month start , month end for last 12 months (may 2020 to April 2021)
I wanted to dynamically show the last 6 month as reporting period (Nov 2020 - April 2021) as reporting period column and period start date column as 11/01/ 2020 and Period end date column as 04/30/ 2021, similarly the previous 6 months starting from (may 2020 - oct 2020) as reporting period column with period start date as 05/01/2020 and period end date as 10/30/2020, any insights on this is appreciated