Generate 20 weeks in calendar

[jmialoundama]

LET v_date_start_calendar = YearStart(AddYears(today(), -1));

LET v_date_end_calendrier =YearEnd(today());

LET v_nb_week_calendar = round((v_date_end_calendrier - v_date_start_calendar) / 7);

LET v_date_start_calendar_week = WeekStart(Today() - 7 * (v_nb_week_calendar-1));

LET v_nb_month_calendrier = Num(((year(Today())*12)+month(Today()))-((year(v_date_start_calendar)*12)+month(v_date_start_calendar))+1);

LET v_date_start_calendrier_mois = MonthStart(Today(), - v_nb_month_calendrier + 1);

[mayuringale25 · Accepted solution]

Hi @jmialoundama ,

// Define the start and end dates for the calendar
LET vStartDate = Date(YearStart(AddYears(Today(), -1)), 'YYYY-MM-DD');
LET vEndDate = Date(YearEnd(Today()), 'YYYY-MM-DD');

// Create a calendar table with one row for each date
Calendar:
LOAD
Date($(vStartDate) + RowNo() - 1) AS Date
AUTOGENERATE $(vEndDate) - $(vStartDate) + 1;

// Add week number and year fields to the calendar table
Left Join (Calendar)
LOAD
Date,
Week(Date) AS WeekNumber,
Year(Date) AS Year
RESIDENT Calendar;

// Select the last 20 weeks of data
CalendarWeeks:
LOAD DISTINCT
WeekNumber,
Year
RESIDENT Calendar
WHERE WeekStart(Date) >= WeekStart(AddWeeks(Today(), -20));

// Drop the original calendar table
DROP TABLE Calendar;

[jmialoundama]

Hi @mayuringale25 ,

Thanks you so mucuh for your help. It's work 🙂