FiscalWeek issue

[nmalla]

Hello everyone.

I need your support to fix my issue. I got the following code to create a Master Calendar.

------------------------------------------------------------------------------------------------------------

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

Temp:

Load

min(Date) as minDate,

max(Date) as maxDate

Resident SalesDetails;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:
LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS Date,
Ceil((WeekStart(TempDate,0,0)-YearStart(TempDate,0,1))/7) as WeekNumber,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

--------------------------------------------------------------------------------------------------------------------------

This calendar is based on my SalesDetail table， which has data from 1 Jan 2022 to till date. 

With the above code Years 2022 and 2023 are giving me the correct WeekNumber but for year 2024, it is not correct.

Jan 1, 2023, is Week 0 but the rest is fine. Jan 1 to Jan7, 2024 is week 0 and Jan 8 is Week 1.

[mruehl]

which output do you expect ?

Maybe this helps:

BrokenWeeks | QlikView Help

[nmalla]

@mruehl 
Thank you for your response.
I saw the link you provided.
But I do not have any idea where to put the set expression.

Set BrokenWeeks=0; //(use unbroken weeks)

Set BrokenWeeks=1; //(use broken weeks)

I am having an issue with the year 2024, where Jan 1 to Jan 7 is week 0.

[mruehl]

Just put in the the load-script before generating the calendar. 

[nmalla]

I set it as below, but still 01Jan 2024 to 07Jan 2024 is Week 0

-------------------------------------------------------------------

QuartersMap:

MAPPING LOAD

rowno() as Month,

'Q' & Ceil (rowno()/3) as Quarter

AUTOGENERATE (12);

//Set BrokenWeeks=0; //(use unbroken weeks)

Set BrokenWeeks=1; //(use broken weeks)

Temp:

Load

min(Date) as minDate,

max(Date) as maxDate

Resident SalesDetails;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

$(varMinDate) + Iterno()-1 As Num,

Date($(varMinDate) + IterNo() - 1) as TempDate

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

TempDate AS Date,

Ceil((WeekStart(TempDate,0,0)-YearStart(TempDate,0,1))/7) as WeekNumber,

//week(TempDate) As Week,

Year(TempDate) As Year,

Month(TempDate) As Month,

Day(TempDate) As Day,

YeartoDate(TempDate)*-1 as CurYTDFlag,

YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

date(monthstart(TempDate), 'MMM-YYYY') as MonthYear,

ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as WeekYear,

WeekDay(TempDate) as WeekDay

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

[Rohan]

Hi @nmalla ,

I have tried this,

Ceil((WeekStart(TempDate,0,0)-YearStart(TempDate,0,1))/7) as WeekNumberOld,
floor(((TempDate)-YearStart(TempDate,0,1))/7)+1 as WeekNumberNew;

Check this out :

Let me know if it works for you.

Regards,

Rohan.

[nmalla]

@Rohan 
Thank you for your time and support.
It gives me the correct result for the Year 2024 but the year 2022 and 2023 is a bit different. In the year 2022, Week 1 should be 3 Jan to 9 Jan and similarly, Week52 should be 26 Dec 2022 to 1 Jan 2023.
With the above-suggested code week 2 for the year, 2022 starts on 8 Jan instead of 10 Jan.
Would you mind checking it for me, please?

[mruehl · Accepted solution]

this worked for me:

Set BrokenWeeks=0;
Set ReferenceDay=0;
Set FirstWeekDay=0;

dont forget to modify your week:  week(TempDate) as WeekNumber_New,

with my european settings it worked as well using none of the SETs.

[nmalla]

Thanks @mruehl  @Rohan 

It was very helpful.

 Week(weekstart(TempDate)) as WeekNumber, with the combination of
Set BrokenWeeks=0;
Set ReferenceDay=0;
Set FirstWeekDay=0;  got the correct value.

Once again thank you so much for your time.