Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
mja_jami
Contributor III
Contributor III

Use of custom weeks in Calendar and compare current vs prior period

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.

%DateFiscal_Week_NoFiscal_Week_StartsFiscal_Week_EndsFiscal_Year
12/26/20185212/21/201812/27/20182018
12/27/20185212/21/201812/27/20182018
12/28/2018112/28/20181/3/20192019
12/29/2018112/28/20181/3/20192019
12/30/2018112/28/20181/3/20192019
12/31/2018112/28/20181/3/20192019
1/1/2019112/28/20181/3/20192019
12/20/20195212/20/201912/26/20192019
12/26/20195212/20/201912/26/20192019

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.

Labels (1)
0 Replies