Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
soha1902
Creator
Creator

How to create weeks from Years???

Hi All,

I am having one Year dimension which contains some year values like 2013, 2014 and 2015.

Now I want to create weeks for each year value.

For example: For year 2015 starting week is 1/05/2015 and last week will be 12/28/2015. so I need to display all the weeks for year 2015.

Weeks
1/05/2015
1/12/2015
----------------
12/28/2015

Please help to find this.

Thanks.

1 Solution

Accepted Solutions
its_anandrjs

Hi,

If you are asking about the Weekstart for all years that yo have in the source table that is 2013,2014,2015, then by the help of year field first created the Yearstart and YearEnd variables. After this create the table with  auto generate the date for calculation of the weekstart in another table as master calendar.

Source:

LOAD * Inline

[

Year

2013

2014

2015

];

NoConcatenate

SourceNew:

LOAD Year Resident Source Order By Year asc;

DROP Table Source;

LET vYrStart = NUM(YearStart(MakeDate( Peek('Year',0,'SourceNew'))));

LET vYrEnd = NUM(YearEnd( MakeDate( Peek('Year',-1,'SourceNew'))));

Tmp:

LOAD

$(vYrStart) + IterNo() -1 as NumDate,

Date($(vYrStart) + IterNo() -1) as Date

AutoGenerate 1 While $(vYrStart) + IterNo() -1 <= $(vYrEnd);

MasterCalendar:

LOAD

*,

Year(Date) as Year,

Month(Date) as Month,

Day(Date) as Day

Resident Tmp;

SourceTab:

LOAD Distinct

Year(Date) as Year,

WeekStart(Date) as WkStart

Resident Tmp;

DROP Table Tmp;

Right Join

LOAD * Resident SourceNew;

DROP Table SourceNew;

And final fields that yo get for WeekStart i create another table with Year and Weekstart as you need for all years because if you keep weekstart in the master calendar is also ok but you need to use distinct if use in master calendar because for every weekstart 7 rows created then. It depends on you how you use it.

Weekstart.png

Regards

Anand

View solution in original post

5 Replies
alexandros17

LET varMinDate = Num(MakeDate(2015, 1 ,1));
LET varMaxDate = Num(MakeDate(2015,12,31));
//*************** Temporary Calendar ***************
TempCalendar:
LOAD
$(varMinDate)+IterNo()-1 AS Num,
Date($(varMinDate)+IterNo()-1,'DD/MM/YYYY') AS TempDate
AUTOGENERATE 1 WHILE $(varMinDate)+IterNo()-1<= $(varMaxDate);

//*************** Master Calendar ***************
MasterCalendar:
NoConcatenate
LOAD
TempDate,
WeekYear(TempDate) * 100 + week(TempDate) AS YearWeek,
WeekYear(TempDate) AS Year,
Year(TempDate) * 100 + month(TempDate) AS YearMonth,
day(TempDate) AS Day,
weekday(TempDate) AS WeekDay,
Week(TempDate) as Week
RESIDENT TempCalendar
ORDER BY TempDate Asc;
DROP Table TempCalendar;

Not applicable

sorry, but I understand you want to create only for the year 2015.

If you use if (date >=  week (year)) would not solve?

or

simply but a little statico follows the example below:

Datastmp:

LOAD * INLINE [

    Date

    '01/01/2015'

    '01/05/2015'

    '28/12/2015'

    '29/12/2015'

];

Data:

Load if(Date >= '01/05/2015' and Date <= '28/12/2015', week(Date)) as week2015 resident Datastmp;

drop Table Datastmp;

its_anandrjs

Hi,

If you are asking about the Weekstart for all years that yo have in the source table that is 2013,2014,2015, then by the help of year field first created the Yearstart and YearEnd variables. After this create the table with  auto generate the date for calculation of the weekstart in another table as master calendar.

Source:

LOAD * Inline

[

Year

2013

2014

2015

];

NoConcatenate

SourceNew:

LOAD Year Resident Source Order By Year asc;

DROP Table Source;

LET vYrStart = NUM(YearStart(MakeDate( Peek('Year',0,'SourceNew'))));

LET vYrEnd = NUM(YearEnd( MakeDate( Peek('Year',-1,'SourceNew'))));

Tmp:

LOAD

$(vYrStart) + IterNo() -1 as NumDate,

Date($(vYrStart) + IterNo() -1) as Date

AutoGenerate 1 While $(vYrStart) + IterNo() -1 <= $(vYrEnd);

MasterCalendar:

LOAD

*,

Year(Date) as Year,

Month(Date) as Month,

Day(Date) as Day

Resident Tmp;

SourceTab:

LOAD Distinct

Year(Date) as Year,

WeekStart(Date) as WkStart

Resident Tmp;

DROP Table Tmp;

Right Join

LOAD * Resident SourceNew;

DROP Table SourceNew;

And final fields that yo get for WeekStart i create another table with Year and Weekstart as you need for all years because if you keep weekstart in the master calendar is also ok but you need to use distinct if use in master calendar because for every weekstart 7 rows created then. It depends on you how you use it.

Weekstart.png

Regards

Anand

View solution in original post

colin_albert

There is a detailed discussion for Week Numbers here.

http://community.qlik.com/blogs/qlikviewdesignblog/2014/01/27/week-numbers

This can get complex as Week1 can start in the previous year, and week 52/53 can end in the following year, so you usually need a WeekYear field as well as WeekNo.

MarcoWedel

Hi,

one solution could be:

LOAD MakeWeekDate(Year, IterNo()) as Week

Inline [

Year

2013

2014

2015

]

While WeekYear(MakeWeekDate(Year, IterNo()))=Year;

QlikCommunity_Thread_148349_Pic1.JPG

hope this helps

regards

Marco