Week

[smilingjohn]

Hi Iam using the below script for master calendar

MaxMin:

LOAD Max(Date) as MaxDate,

  Min(Date) as MinDate

Resident FluData;

SET vFiscalYearStartMonth = 4;

let vStartDate=Peek('MinDate');

let vEndDate=Peek('MaxDate');

FiscalCalendar:

LOAD

*,

Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter

Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name

LOAD

*,

Year(Date) AS Year, // Standard Calendar Year

Month(Date) AS Month, // Standard Calendar Month

Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name

//Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter

'Q' & Ceil(Month(Date)/3) & '-' & Year(Date) as Quarter,

Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal  Month

Right(YearName(Date,0,$(vFiscalYearStartMonth)),4)as FiscalYear,

date(MonthStart(Date),'MMM-YY') AS MonthYear,

YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYearr;  // Fiscal Year

LOAD

Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,

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

AutoGenerate vEndDate - vStartDate + 1;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

Which is perfectly working

Can someone please tell me how do i even break the date by Week in the same calendar ?

[Chanty4u]

chk dis

Week creation

Need a Calender with week and Month

[tresesco]

Week(Date) as Week

[smilingjohn]

Hi I tried Week(Date) as Week

but it is converting week only for the month of March2016 , not for others ?

[smilingjohn]

Week is not working for my date ,

[#6]

Hi John,

Can you please try the below solution and let me know is it working.

Interval(Date-YearStartDate[Date],'d') By using this you will get the value of number of days after divide by 7 you will get the week value

Thanks,
Sreeman

[tamilarasu · Accepted solution]

Hi,

You can use Week(Date) as Week as mentioned by Tresesco. Below script working fine.

MaxMin:
LOAD Max(Date) as MaxDate,
  Min(Date) as MinDate
Resident FluData;

SET vFiscalYearStartMonth = 4;
let vStartDate= Peek('MinDate');
let vEndDate=  Peek('MaxDate');

FiscalCalendar:
LOAD
*,
Dual('Q' & Ceil(FiscalMonth/3), Ceil(FiscalMonth/3)) AS FiscalQuarter, // Fiscal Calendar Quarter
Dual(Text(Date(MonthEnd(Date), 'MMM')), FiscalMonth) AS FiscalMonthName; // Fiscal Calendar Month Name
LOAD
*,
Year(Date) AS Year, // Standard Calendar Year
Month(Date) AS Month, // Standard Calendar Month
Week(Date) As Week,    //==>> Week 
Date(MonthEnd(Date), 'MMM') AS MonthName,  // Standard Calendar Month Name
//Dual('Q' & Ceil(Month(Date)/3), Ceil(Month(Date)/3)) AS Quarter,  // Standard Calendar Quarter
'Q' & Ceil(Month(Date)/3) & '-' & Year(Date) as Quarter,
Mod(Month(Date) - $(vFiscalYearStartMonth), 12)+1 AS FiscalMonth,  // Fiscal  Month
Right(YearName(Date,0,$(vFiscalYearStartMonth)),4)as FiscalYear,
date(MonthStart(Date),'MMM-YY') AS MonthYear,
YearName(Date, 0, $(vFiscalYearStartMonth)) AS FiscalYearr;  // Fiscal Year
LOAD
Date($(vStartDate) + RangeSum(Peek('RowNum'), 1) - 1) AS Date,
RangeSum(Peek('RowNum'), 1) AS RowNum
AutoGenerate vEndDate - vStartDate + 1;