Skip to main content
Announcements
Qlik Connect 2025: 3 days of full immersion in data, analytics, and AI. May 13-15 | Orlando, FL: Learn More
cancel
Showing results for 
Search instead for 
Did you mean: 
smilingjohn
Specialist
Specialist

Week

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 ?

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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;

View solution in original post

6 Replies
tresesco
MVP
MVP

Week(Date) as Week

smilingjohn
Specialist
Specialist
Author

Hi I tried Week(Date) as Week

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

smilingjohn
Specialist
Specialist
Author

Week is not working for my date ,

Not applicable

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
Champion
Champion

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;