Week creation

[nareshthavidishetty]

Hi,

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.

Thanks....

[nareshthavidishetty · Accepted solution]

Thanks..Issue got resolved below is the script

LOAD *,

  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,

  RangeSum(Peek('RowNum'), 1) AS RowNum

AutoGenerate vEndDate - vStartDate + 1;