topic Re: How to create weeks from Years??? in QlikView

How to create weeks from Years???

soha1902 — Wed, 14 Jan 2015 10:49:15 GMT

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.

Re: How to create weeks from Years???

alexandros17 — Wed, 14 Jan 2015 11:05:04 GMT

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;

Re: How to create weeks from Years???

Wed, 14 Jan 2015 11:12:06 GMT

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

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

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;

Re: How to create weeks from Years???

its_anandrjs — Wed, 14 Jan 2015 12:15:37 GMT

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.

Regards

Anand

Re: How to create weeks from Years???

Colin-Albert — Wed, 14 Jan 2015 12:24:52 GMT

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.

Re: How to create weeks from Years???

MarcoWedel — Wed, 14 Jan 2015 12:42:20 GMT

Hi,

one solution could be:

LOAD MakeWeekDate(Year, IterNo()) as Week
Inline [
Year
2013
2014
2015
]
While WeekYear(MakeWeekDate(Year, IterNo()))=Year;

hope this helps

regards

Marco