topic Re: Generate Week Start Date in QlikView

Generate Week Start Date

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

I have Year Field with Years, Week Day field with what day of the week it is and Week Number field with Week numbers. I need to create a field that shows the week number and the starting date of each week. My week starts on Monday and ends on Sunday. How to achieve this?

Below is the sample data

Thank you

Re: Generate Week Start Date

Anil_Babu_Samineni — Wed, 27 Dec 2017 16:48:16 GMT

Not sure, I understand this. Perhaps this?

WeekStart(MakeDate(Year))

Re: Generate Week Start Date

devarasu07 — Wed, 27 Dec 2017 17:01:16 GMT

Hi,

try to do below steps,

in your script main tab there is pre-deined variable declaration section here u can update FirstWeekDay to 0 instead 6

and then BrokenWeek as 0 instead of then then it should give u the desired result like attached.

//SET FirstWeekDay=6;

//SET BrokenWeeks=1;

SET FirstWeekDay=0; // for ISO week number, week starts on monday

SET BrokenWeeks=0; // week starts from the begining of the year

sample calendar script

T_MinMax:
LOAD
    Min(FieldValue('Date',RecNo())) as MinDate,
    Max(FieldValue('Date',RecNo())) as MaxDate
AutoGenerate FieldValueCount('Date');

LET vMinDate = num(Peek('MinDate'));
LET vMaxDate = num(Peek('MaxDate'));
LET vToday = $(vMaxDate);


DROP Table T_MinMax;


Calendar:
LOAD CalDate as Date,
    Day(CalDate) as Day,
    Week(CalDate) as Week,
    WeekDay(CalDate) as WeekDay,
    Month(CalDate) as Month,
    'Q' & Ceil(Month(CalDate)/3) as Quarter,
    Date(MonthStart(CalDate), 'MM-YYYY') as MonthYear,
        num(month(date#(CalDate,'MMM'))) as MonthNo,
    Week(weekstart(CalDate)) & '-' & WeekYear(CalDate) as WeekYear,
    Year(CalDate) as Year,  
    InMonthToDate(CalDate,$(vToday),0) * -1 as CurrMTDFlag,
InMonthToDate(CalDate,$(vToday),-12) * -1 as LastMTDFlag,
    InYearToDate(CalDate,$(vToday),0) * -1 as CurrYTDFlag,
InYearToDate(CalDate,$(vToday),-1) * -1 as LastYTDFlag;
LOAD Date($(vMinDate) + IterNo()) as CalDate
AutoGenerate 1 
While $(vMinDate) + IterNo() <= $(vMaxDate);

for more details check this

https://qlikcommunity.qliktech.com/blogs/qlikviewdesignblog/2014/01/21/week-start?author=2012/09

Calendars

Re: Generate Week Start Date

qlikwiz123 — Wed, 27 Dec 2017 17:25:13 GMT

This is not returning the right date.I need the Start Date for each week. For example, For Year 2017 and Week 1, I should get the date as 01/02/2017 since Monday is 2nd January. Similarly, for week 2, I should get the date as 01/09/2017.

Re: Generate Week Start Date

qlikwiz123 — Wed, 27 Dec 2017 17:25:31 GMT

Re: Generate Week Start Date

qlikwiz123 — Wed, 27 Dec 2017 17:38:57 GMT

You have Order Date Field which you are using as your Date column. But as you can see my tables, I don't have a Date Field. All I have is Year, Week Numbers. So I have to generate dates, so that for every week, I can show the Start Date. When I pick week 1 in 2017, the StartDate should be 01/02/2017 and so on...

Re: Generate Week Start Date

mgavidia — Wed, 27 Dec 2017 22:52:49 GMT

You can get this table by inserting the MakeWeekDate function in your loading script as shown below.

Re: Generate Week Start Date

Anil_Babu_Samineni — Thu, 28 Dec 2017 00:59:32 GMT

Then MakeWeekDate() should return

Re: Generate Week Start Date

qlikwiz123 — Thu, 28 Dec 2017 14:19:59 GMT

This requires me to create an Excel and pull the dates from it. This process if difficult as I need to define dates for all 365 days for just one year. It becomes impossible if I need the same for multiple years. Any other solution?

Re: Generate Week Start Date

qlikwiz123 — Thu, 28 Dec 2017 15:14:18 GMT

Thank you Anil

Re: Generate Week Start Date

MK9885 — Thu, 28 Dec 2017 15:19:58 GMT

Maybe the below one?

Not sure if correct, I did 2 preceding load.... Maybe there is a better way to do it?

Since you've no dates, I did preceding load on original table as

WeekDay(MakeWeekDate(Year,Week,0)) as WD,

After this I did one more preceding load in same table

MakeWeekDate(Year,WD,0) as WeekDate1;

In script it would be

Calendar:

Load*,

MakeWeekDate(Year,WD,0) as WeekDate1; //////Second Preceding Load

Load*,

WeekDay(MakeWeekDate(Year,Week,0)) as WD; //////First Preceding Load

Load

.........

....

From....;

But this is only for 2017 year. If you want to have more years then when you select Week 1 it would show Monday for all the years.

For multiple years, you have to select a Year first and then week.

Re: Generate Week Start Date

mgavidia — Thu, 28 Dec 2017 16:33:10 GMT

I am glad you figure it out.

Just to be clear, my version solution does not required to previously create dates in Excel.

The parameters in the MakeWeekDate function are taking from previously loaded date from any source.

he table on the left is from the result in QlikView not from Excel.