topic Week in QlikView

Week

smilingjohn — Wed, 25 Nov 2020 16:16:04 GMT

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 ?

Re: Week

Chanty4u — Thu, 31 Mar 2016 12:05:10 GMT

chk dis

Week creation

Need a Calender with week and Month

Re: Week

tresB — Thu, 31 Mar 2016 12:07:33 GMT

Week(Date) as Week

Re: Week

smilingjohn — Thu, 31 Mar 2016 13:11:06 GMT

Hi I tried Week(Date) as Week

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

Re: Week

smilingjohn — Thu, 31 Mar 2016 13:21:47 GMT

Week is not working for my date ,

Re: Week

Thu, 31 Mar 2016 13:45:01 GMT

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

Re: Week

tamilarasu — Fri, 01 Apr 2016 02:26:05 GMT

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;