topic Re: Redefining the Week Numbers in QlikView

Redefining the Week Numbers

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

Hello,

Why my week is not working Properly, Up to Year end should show week 52/53 and then should be start the week from 1 Again.

Redefining the Week Numbers

Can you please help me.

Re: Redefining the Week Numbers

Anonymous — Mon, 01 Aug 2016 13:13:54 GMT

there is a definition which week have to be week 1 (see Wikipedia).

so it may be that some of the Dates of end of december are already in week 1

sometimes the first Dates of a year can be week 52/53!

if you Need a different calendar you may e.g. create virtual week 53v if your business requries it

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Mon, 01 Aug 2016 13:16:50 GMT

Rudolf, Thanks for your Suggestion. Did you get a chance to look to see that URL?

Please find my image, In that how HIC is calculating for Broken Weeks Possible?

Re: Redefining the Week Numbers

Anonymous — Mon, 01 Aug 2016 13:26:31 GMT

I donot understand? what is HIC?

If you want a calendar like "broken weeks possible" you Need to create your own mastercalendar with a column week and create your own week

e.g. if (week(yourdate)=1 AND month(yourdate='dec'), 53, week(yourdate)) as weekbroken

my customer expects exactly this behaviour and I realized it this way with virtual weeks (some years have real week 53, so no virtual week needed)

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Mon, 01 Aug 2016 13:32:51 GMT

HIC Means --

Henric Cronström

Which i share the Link, He only created that..

Can you please help on my Calendar

Re: Redefining the Week Numbers

Anonymous — Mon, 01 Aug 2016 13:50:05 GMT

add to to your mastercalendar (my enhancement):

if (Week(yourdate)=1 AND DAY(yourdate)> 20, // test for week 1 in december
53, // force virtual week 53

week(yourdate) as week

all other fields (month, year, day) can remain in your ,mastercalender

only week Need to be adjusted

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Mon, 01 Aug 2016 13:58:27 GMT

See this, My master calendar looks like this

MasterCalendar:

LOAD TempDate AS "MainDate",

week(TempDate) AS Week,

year(TempDate) AS Year,

Day(TempDate) AS Day,

month(TempDate) AS Month,

weekday(TempDate) AS WeekDay,

RESIDENT TempCalendar;

drop table TempCalendar;

Please tell me where i need to Add that?

Re: Redefining the Week Numbers

Anonymous — Mon, 01 Aug 2016 14:08:02 GMT

try this code:

MasterCalendar:

LOAD TempDate AS "MainDate",

if (Week(TempDate)=1 AND month(TempDate)='Dec', // test for week 1 in december
53, // force virtual week 53

week(TempDate) as Week

year(TempDate) AS Year,

Day(TempDate) AS Day,

month(TempDate) AS Month,

weekday(TempDate) AS WeekDay,

RESIDENT TempCalendar;

drop table TempCalendar;

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Mon, 01 Aug 2016 14:10:03 GMT

If i use that if (Week(TempDate)=1 AND month(TempDate)='Dec' -- How to Close If {Bracket} here. Getting error

Re: Redefining the Week Numbers

Anonymous — Mon, 01 Aug 2016 15:12:46 GMT

could not test Expression, but ending bracket was missing before as

MasterCalendar:

LOAD TempDate AS "MainDate",

if (Week(TempDate)=1 AND month(TempDate)='Dec', // test for week 1 in december
53, // force virtual week 53

week(TempDate)) as Week

year(TempDate) AS Year,

Day(TempDate) AS Day,

month(TempDate) AS Month,

weekday(TempDate) AS WeekDay,

RESIDENT TempCalendar;

drop table TempCalendar;

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Mon, 01 Aug 2016 15:37:44 GMT

Hello Sir,

I didn't get any difference? Can you please have a look which you shared before and Recent, Please?

Re: Redefining the Week Numbers

Anonymous — Mon, 01 Aug 2016 15:41:14 GMT

canyou provide only script with your mastercalender?

I will then check

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Mon, 01 Aug 2016 15:44:51 GMT

I already attached with qvw. Anyway, Please find the Script

Re: Redefining the Week Numbers

Anonymous — Mon, 01 Aug 2016 15:59:27 GMT

I tried with your script. for 12/29/2014 I get a 53 instead of 1

LET varMinDate = 40909;
LET varMaxDate = 42735;
//LET varMinDate = Num(Peek('MinDate', 0, 'TEMP'));
//LET varMaxDate = Num(Peek('MaxDate' , 0, 'TEMP'));

LET vToday = Num(today());

//drop Table TEMP;

TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1) AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

MasterCalendar:
NoConcatenate LOAD TempDate AS "MainDate",
num(TempDate) AS SummaryDate,
if (week(TempDate)=1 and month(TempDate)='Dec', 53, week(TempDate)) as Week,
// week(TempDate) AS Week,
year(TempDate) AS Year,
month(TempDate) AS Month,
date(TempDate,'MM') as MonthNum,
day(TempDate) AS Day,
day(TempDate)-1 AS PreviousDay,
weekday(TempDate) AS WeekDay,
'Q' & Ceil(month(TempDate)/3) as Quarter,
date(monthstart(TempDate), 'MMM-YYYY') AS MonthYear,
week(TempDate)&'-'&Year(TempDate) AS WeekYear,
Year2Date(TempDate, 0, 1, $(vToday))*-1 AS CurYTDFlag,
Year2Date(TempDate,-1, 1, $(vToday))*-1 AS LastYTDFlag,
1 as temp
RESIDENT TempCalendar;

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Mon, 01 Aug 2016 16:28:58 GMT

Yes, Correct. I am looking this

But, Here i want to start week from Monday. See for 1st week till sat is there, But, i want to move 2nd Week Sunday to 1st week and then 2nd Week Should start from Monday Onwords.

I Apologize my English

Updated: Expecting - Every week starting from Monday

Week	Mon	Tue	Wed	Thu	Fri	Sat	Sun
W26	27	28	29	30	01	02	03
W27	04	05	06	07	08	09	10
W28	11	12	13	14	15	16	17
W29	18	19	20	21	22	23	24
W30	25	26	27	28	29	30	31

Re: Redefining the Week Numbers

Anonymous — Tue, 02 Aug 2016 07:19:23 GMT

normal week start on sunday. if you Need that sunday belongs to previous week you Need to adjust the week

if (weekday(TempDate)='Su', week(TempDate)-1, week(TempDate)) as Week

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Tue, 02 Aug 2016 07:27:13 GMT

Yes, as per our adjust it will go.

As Reference, Please find image

Note: Here, i want to show 27, 28, 29, 30, 31 for 53 weeks Because Upto December 2015 the weeks are finished. So, here after that I want to show 1, 2, 3 dates for 1st week in 2016 and 4-10 should be 2nd week, Like that...

Thanks you so much for your Response

Re: Redefining the Week Numbers

Anil_Babu_Samineni — Tue, 02 Aug 2016 15:45:27 GMT

Rudolf, I got it. Thanks for your Help

I's working with this code

Let vCal_FD = 0; // First Day of the week {0=Mon, 1=Tue, ... , 6=Sun}

Let vCal_BW = 0; // Broken Weeks allowed {0=No, 1=Yes}

Let vCal_RD = 1; // Reference day = This day in Jan defines week one {1..7}

Load

if(TempDate < WeekRefDate and WeekNumber=0 and year(TempDate)<WeekYear,53,WeekNumber+1) as Actual_WeekNumber;

Load *,

Div( TempDate - WeekStart( WeekYearRefDate, 1, $(vCal_FD) ) + 7, 7 ) as WeekNumber,

Year( WeekYearRefDate ) as WeekYear;

Load *,

Date( YearStart( If( $(vCal_BW), TempDate, WeekRefDate )) + $(vCal_RD) - 1) as WeekYearRefDate ;

Load *,

Date( WeekStart( TempDate , 1, $(vCal_FD) ) - $(vCal_RD) ) as WeekRefDate ;

Week	Mon	Tue	Wed	Thu	Fri	Sat	Sun
W26	27	28	29	30	01	02	03
W27	04	05	06	07	08	09	10
W28	11	12	13	14	15	16	17
W29	18	19	20	21	22	23	24
W30	25	26	27	28	29	30	31

Week	Mon	Tue	Wed	Thu	Fri	Sat	Sun
W26	27	28	29	30	01	02	03
W27	04	05	06	07	08	09	10
W28	11	12	13	14	15	16	17
W29	18	19	20	21	22	23	24
W30	25	26	27	28	29	30	31

Week	Mon	Tue	Wed	Thu	Fri	Sat	Sun
W26	27	28	29	30	01	02	03
W27	04	05	06	07	08	09	10
W28	11	12	13	14	15	16	17
W29	18	19	20	21	22	23	24
W30	25	26	27	28	29	30	31