Dear Qlik experts,
With my very limited knowledge and the help of calendar scripts on community I created this calendar and joined custom calendar weeks (in excel) which has Week starts and ends and therefore the weeks are different than the regular calendar still has 52 weeks a year.
| %Date | Fiscal_Week_No | Fiscal_Week_Starts | Fiscal_Week_Ends | Fiscal_Year |
| 12/26/2018 | 52 | 12/21/2018 | 12/27/2018 | 2018 |
| 12/27/2018 | 52 | 12/21/2018 | 12/27/2018 | 2018 |
| 12/28/2018 | 1 | 12/28/2018 | 1/3/2019 | 2019 |
| 12/29/2018 | 1 | 12/28/2018 | 1/3/2019 | 2019 |
| 12/30/2018 | 1 | 12/28/2018 | 1/3/2019 | 2019 |
| 12/31/2018 | 1 | 12/28/2018 | 1/3/2019 | 2019 |
| 1/1/2019 | 1 | 12/28/2018 | 1/3/2019 | 2019 |
| 12/20/2019 | 52 | 12/20/2019 | 12/26/2019 | 2019 |
| 12/26/2019 | 52 | 12/20/2019 | 12/26/2019 | 2019 |
I used calendar script as below and left joined my excel sheet to point the weeks. Now the trouble is I cannot use all the calendar functions, such as month. If I use them gives me wrong data.
Dates:
Load Distinct %Date,
Date,
Year(Date) as Cal_Year,
Monthname(Date) as [Month Year],
Month(Date) as Month,
WeekDay(Date) as Day,
WeekStart(Date,0,4) as WeekStart,
WeekEnd(Date,0,4)as WeekEnd,
Num(Month(Date)) AS 'MonthNum',
date(floor(monthend(Date))) as MonthEndDate,
(Year(Date) * 100) + num(Month(Date)) as YearMonthNo,
(Year(Date) * 12) + num(Month(Date)) as YearMonthSeq
Resident TempDates
Order by Date;
left Join(Dates)
Load min(WeekStart) as FirstWeek,
Cal_Year
Resident Dates
Group by Cal_Year;
Left Join(Dates)
Load *,
(Year(WeekStart) * 100) + Cal_WeekNo as YearWeekNo;
// Load ((WeekStart-FirstWeek)/7) + 1 as WeekNo,
Load ((WeekStart-FirstWeek)/7) as Cal_WeekNo,
WeekStart
Resident Dates;
left join SP_Calendar:
LOAD
%Date,
// Fiscal_Week_No as WeekNo,
Fiscal_Week_No as Week,
Fiscal_Week_Starts as SP_Week_Starts,
Fiscal_Week_Ends AS SP_Week_Ends,
Fiscal_Year as Year,
Fiscal_Quarter AS Quarter,
FiscalYR_Wk as SP_YR_Wk
FROM [lib://AttachedFiles/SP Calendar.xlsx]
(ooxml, embedded labels, table is Sheet2);
Additionally,
I’ve found and used sum($(vSETCY)premium)// {$<Year={$(=max(total Year))}>} for current year’s premium and sum( $(vSETPY) premium) for prior year// {$<Year={$(=max(total Year)-1)}>} in line chart.
Is my Calendar script okay? Is there any better way to assign my customized weeks with the calendar? How can I show current year/ month vs prior year/ month calculation in line chart lets say.
Thank you very much for your time, effort and help.
