Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

First full week of the month

Hi

I create the following table in my load script:

yearmonthcalendarweek
2017727
2017726
.........

Now I'd like to classify the calendarweek as first full week of the month. Week 27 for example in July 2017 would constitute a full week of that month (7 days) and week 26 wouldn't because only Saturday and Sunday are part of July.

Can you help me out?

Kind Regards

Daniel

1 Solution

Accepted Solutions
Not applicable
Author

Hey guys

Thanks for all the answers. I found a solution myself that kind of corresponds with what you suggested:

First I find out the max and min date in my data. Then I create a calendar and count the days in the weeks of the month. Consequently a flag indicates the full week.

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

/*generate year, week, month, etc. based on list of dates*/  

MasterCalendar: 

Load 

TempDate AS OrderDate, 

week(TempDate) As Week, 

Year(TempDate) As Year, 

Month(TempDate) As Month

Resident TempCalendar

Order By TempDate ASC; 

Drop Table TempCalendar; 

/*Count how many days are within the weeks of the respective month*/

temp_cal_1:

NoConcatenate LOAD Distinct Year,

                   Month,

                   Week,

                   Count(OrderDate) AS ANZ_TAGE_MONAT

Resident MasterCalendar

Group By Year,

         Month,

         Week

;

Kind Regards

Daniel

View solution in original post

6 Replies
Anonymous
Not applicable
Author

where do you need it, in script (in a separate table or existing table) or in frontend?!?

Not applicable
Author

Hi Robin

Thanks for the reply.

I'd like to classify in the load script because I need it for further calculation. Meaning a forth column "FLAG_FULL_WEEK" which equals 1 if it is a full week of the month and 0 when not.

Regards

Daniel

tomasz_tru
Specialist
Specialist

Try this way:

(I used wednesday as a reference day, get start and end of the week, get it's month number and check whether it's the same

output:

Load *,

if(

((Num(Month(WeekStart(MakeWeekDate(year,week,2))))=month)

* (Num(Month(WeekEnd(MakeWeekDate(year,week,2))))=month))

, 'full week', 'partial week') AS Is_partial_week

;

Load * inline [

year, month, week

2017, 7, 27

2017, 7, 26

2017, 8, 34

2017, 8, 35

2017, 9, 35

2017, 9, 36];

Anonymous
Not applicable
Author

SET ThousandSep='.';
SET DecimalSep=',';
SET MoneyThousandSep='.';
SET MoneyDecimalSep=',';
SET MoneyFormat='#.##0,00 €;-#.##0,00 €';
SET TimeFormat='hh:mm:ss';
SET DateFormat='DD.MM.YYYY';
SET TimestampFormat='DD.MM.YYYY hh:mm:ss[.fff]';
SET MonthNames='Jan;Feb;Mrz;Apr;Mai;Jun;Jul;Aug;Sep;Okt;Nov;Dez';
SET DayNames='Mo;Di;Mi;Do;Fr;Sa;So';


for i= 1 to 365
TEMP:
LOAD
Date(MakeDate(2017) + $(i) -1) as DATEFIELD  AutoGenerate 1;
;
next


TABLE:
LOAD
DATEFIELD,
MonthName(DATEFIELD) as YEARMONTH,
Week(DATEFIELD) as WEEK,
WeekYear(DATEFIELD)&'-'&Week(DATEFIELD) as WEEKYEAR,
WeekDay(DATEFIELD) as DAY
Resident TEMP;

DROP Table TEMP;

Left join

LOAD
WeekYear(min(DATEFIELD))&'-'&Week(min(DATEFIELD)) as WEEKYEAR,
'X'
as FULL_WEEK
Resident TABLE
Where DAY= 'Mo'
Group by YEARMONTH
;

MarcoWedel

Hi,

another solution might be:

QlikCommunity_Thread_272492_Pic1.JPG

table1:

LOAD *,

    -(Month(WeekName)=Month(WeekName+6)) as FLAG_FULL_WEEK;

LOAD year,

    month,

    calendarweek,

    WeekName(MakeWeekDate(year+(calendarweek>51 and month=1)-(calendarweek=1 and month=12),calendarweek)) as WeekName

FROM [https://community.qlik.com/thread/272492](html, codepage is 1252, embedded labels, table is @1, filters(Remove(Row, Pos(Top, 4))));

hope this helps

regards

Marco

Not applicable
Author

Hey guys

Thanks for all the answers. I found a solution myself that kind of corresponds with what you suggested:

First I find out the max and min date in my data. Then I create a calendar and count the days in the weeks of the month. Consequently a flag indicates the full week.

TempCalendar: 

LOAD 

$(varMinDate) + Iterno()-1 As Num, 

Date($(varMinDate) + IterNo() - 1) as TempDate 

AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate); 

/*generate year, week, month, etc. based on list of dates*/  

MasterCalendar: 

Load 

TempDate AS OrderDate, 

week(TempDate) As Week, 

Year(TempDate) As Year, 

Month(TempDate) As Month

Resident TempCalendar

Order By TempDate ASC; 

Drop Table TempCalendar; 

/*Count how many days are within the weeks of the respective month*/

temp_cal_1:

NoConcatenate LOAD Distinct Year,

                   Month,

                   Week,

                   Count(OrderDate) AS ANZ_TAGE_MONAT

Resident MasterCalendar

Group By Year,

         Month,

         Week

;

Kind Regards

Daniel