Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

Announcements
Coming your way, the Qlik Data Revolution Virtual Summit. October 27-29. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator II
Creator II

Generate Week Start Date

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

1 Solution

Accepted Solutions
Highlighted

Not sure, I understand this. Perhaps this?

WeekStart(MakeDate(Year))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

11 Replies
Highlighted

Not sure, I understand this. Perhaps this?

WeekStart(MakeDate(Year))

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

Highlighted
Master II
Master II

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

Capture.JPG

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

Highlighted
Creator II
Creator II

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.

Highlighted
Creator II
Creator II

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.

Highlighted
Creator II
Creator II

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

Highlighted
Creator
Creator

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


Highlighted

Then MakeWeekDate() should return

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
Highlighted
Creator II
Creator II

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?

Highlighted
Creator II
Creator II

Thank you Anil