Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 
nmalla
Contributor II
Contributor II

FiscalWeek issue

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.

 

 
 
Labels (1)
1 Solution

Accepted Solutions
mruehl
Partner - Specialist
Partner - Specialist

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.

View solution in original post

8 Replies
mruehl
Partner - Specialist
Partner - Specialist

which output do you expect ?

Maybe this helps:

BrokenWeeks | QlikView Help

nmalla
Contributor II
Contributor II
Author

@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
Partner - Specialist
Partner - Specialist

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

nmalla
Contributor II
Contributor II
Author

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

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 :

Rohan_0-1705731466949.png

 

Let me know if it works for you.

Regards,

Rohan.

 

nmalla
Contributor II
Contributor II
Author

@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
Partner - Specialist
Partner - Specialist

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
Contributor II
Contributor II
Author

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.