topic Re: Redefining Week in QlikView

Redefining Week

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

i have the date field its start from 12-28-2014 want to set this date as 1st week so on...

while using master calendar i can't achieve this in master calendar.

how can i achieve this below output while using the master calendar.

PFA of QVW.

2015		2016
WEEKNO	DATE	WEEKNO	DATE
1	12/28/2014	1	12/27/2015
2	1/4/2015	2	1/3/2016
3	1/11/2015	3	1/10/2016
4	1/18/2015	4	1/17/2016
5	1/25/2015	5	1/24/2016
6	2/1/2015	6	1/31/2016
7	2/8/2015	7	2/7/2016
8	2/15/2015	8	2/14/2016
9	2/22/2015	9	2/21/2016
10	3/1/2015	10	2/28/2016
11	3/8/2015	11	3/6/2016
12	3/15/2015	12	3/13/2016
13	3/22/2015	13	3/20/2016
14	3/29/2015	14	3/27/2016
15	4/5/2015	15	4/3/2016
16	4/12/2015	16	4/10/2016
17	4/19/2015	17	4/17/2016
18	4/26/2015	18	4/24/2016
19	5/3/2015	19	5/1/2016
20	5/10/2015	20	5/8/2016
21	5/17/2015	21	5/15/2016
22	5/24/2015	22	5/22/2016
23	5/31/2015	23	5/29/2016
24	6/7/2015	24	6/5/2016
25	6/14/2015	25	6/12/2016
26	6/21/2015	26	6/19/2016
27	6/28/2015	27	6/26/2016
28	7/5/2015	28	7/3/2016
29	7/12/2015	29	7/10/2016
30	7/19/2015	30	7/17/2016
31	7/26/2015	31	7/24/2016
32	8/2/2015	32	7/31/2016
33	8/9/2015	33	8/7/2016
34	8/16/2015	34	8/14/2016
35	8/23/2015	35	8/21/2016
36	8/30/2015	36	8/28/2016
37	9/6/2015	37	9/4/2016
38	9/13/2015	38	9/11/2016
39	9/20/2015	39	9/18/2016
40	9/27/2015	40	9/25/2016
41	10/4/2015	41	10/2/2016
42	10/11/2015	42	10/9/2016
43	10/18/2015	43	10/16/2016
44	10/25/2015	44	10/23/2016
45	11/1/2015	45	10/30/2016
46	11/8/2015	46	11/6/2016
47	11/15/2015	47	11/13/2016
48	11/22/2015	48	11/20/2016
49	11/29/2015	49	11/27/2016
50	12/6/2015	50	12/4/2016
51	12/13/2015	51	12/11/2016
52	12/20/2015	52	12/18/2016
53	12/25/2016

Re: Redefining Week

Thu, 21 Jul 2016 06:18:45 GMT

Try this pls.

Let vDate=Date(Date#('12/28/2014','MM/DD/YYYY'),'DD/MM/YYYY');
Calendar:
load Date('$(vDate)'+(RowNo()-1)*7) as Date,
if(mod(RowNo(),53)=0,1,
if(Year(Date('$(vDate)'+(RowNo()-1)*7))=2016,
mod(RowNo(),53)+1,
mod(RowNo(),53)
)
) as WeekNo
autogenerate 105;

Re: Redefining Week

migueldelval — Thu, 21 Jul 2016 06:30:34 GMT

Hi Stephen,

Maybe you can find diferent information in:

How to use - Master-Calendar and Date-Values

Custom Calendar Generator‌

Regards

Miguel del Valle

Re: Redefining Week

stephenedberkg — Thu, 21 Jul 2016 06:33:11 GMT

where can i put this script inside the master calendar ?

Re: Redefining Week

Thu, 21 Jul 2016 06:50:49 GMT

in master calendar only

Re: Redefining Week

stephenedberkg — Thu, 21 Jul 2016 07:00:07 GMT

yeah i cant get , each week have several week stat date

Re: Redefining Week

Thu, 21 Jul 2016 07:07:15 GMT

Hi Stephen,

You can try this script as well:

//---------------------------------------------------------------------------

LET vMinDate = 42001; // Start Date

LET vMaxDate = 42729; // End Date

//*****Temp Calendar*****

TempCal:

LOAD

DATE($(vMinDate) + ROWNO() -1) AS TempDate

AUTOGENERATE

$(vMaxDate) - $(vMinDate) + 1;

Calendar:

Load *, AutoNumber(WeekEnd,YearNo) as WeekNo;

Load *, Year(WeekEnd) as YearNo;

Load

Date(WeekEnd(TempDate+1)-1) as WeekEnd

resident TempCal;

Drop Table TempCal;

//---------------------------------------------------------------------------

Please let me know if it helps!

Regards,

Sakir

Re: Redefining Week

stephenedberkg — Thu, 21 Jul 2016 08:48:04 GMT

sakirali‌‌

its working fine when we give the min and max date .

actually my start date is 7-26--15 (32nd week)

i want to show this week in selection field of dashboard

if i hardcore the min and max date it take 7-25-15 as 1 st week. but i want this date start as 32nd week of 2015.

Re: Redefining Week

Thu, 21 Jul 2016 10:15:46 GMT

Hi,

I think even if you are selecting any random date as your start date, you must keep a certain date as a standard from where the calculation should start.

For example, you can simply write:

LET vStartDate= 42211; // 7/26/2015

LET vMinDate = WeekStart(YearStart(Date(vMinDate)))-1;

and then proceed with the above script.

Hope it helps!

Regards,

Sakir