I need to create an week and i get this by week(r_date) as week and it gave weeks from (1 to 53) but i need an week as per month 5 weeks like if user select March means it should display w1,w2,w2,w4,w5 like i need to show.
Dual('Q' & Ceil(FIMonth/3), Ceil(FIMonth/3)) AS FQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(new_order_date), 'MMM')), FIMonth) AS Month, // Fiscal Calendar Month Name
'Week' & pick(match([WeekNumber],0,1,2,3,4),1,2,3,4,5) as Week;
LOAD *,
Year(new_order_date) AS Years, // Standard Calendar Year
Month(new_order_date) AS Months,
day(new_order_date) as Day,
if(month(weekend(new_order_date,0,6))= month(new_order_date),div(day(WeekEnd(new_order_date,0,6)),7),div(day(Weekend(new_order_date,-1,6)),7)) as WeekNumber,
Date(MonthEnd(new_order_date), 'MMM') AS MonthName, // Standard Calendar Month Name
Dual('Q' & Ceil(Month(new_order_date)/3), Ceil(Month(new_order_date)/3)) AS Quarter, // Standard Calendar Quarter
Mod(Month(new_order_date) - $(vFiscalYearStartMonth), 12)+1 AS FIMonth, // Fiscal Calendar Month
YearName(new_order_date, 0, $(vFiscalYearStartMonth)) AS FIYear; // Fiscal Calendar Year
LOAD Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS new_order_date,
