Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
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

Re: First full week of the month

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

6 Replies
robin_hausdoerfer
Valued Contributor III

Re: First full week of the month

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

Not applicable

Re: First full week of the month

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
Valued Contributor

Re: First full week of the month

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];

robin_hausdoerfer
Valued Contributor III

Re: First full week of the month

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
;

Re: First full week of the month

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

Re: First full week of the month

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