Generate Week Start Date

[qlikwiz123]

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

[Anil_Babu_Samineni · Accepted solution]

Not sure, I understand this. Perhaps this?

WeekStart(MakeDate(Year))

[devarasu07]

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

[qlikwiz123]

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.

[qlikwiz123]

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.

[qlikwiz123]

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...

[mgavidia]

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

[Anil_Babu_Samineni]

Then MakeWeekDate() should return

[qlikwiz123]

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?

[qlikwiz123]

Thank you Anil