topic RE:MonthEnd in QlikView

RE:MonthEnd

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

Hi All,

Settu Periyasamy

i am facing one issue regarding week and month

i have fields like week number 1 to 52 and months december,january,February

so issue is : in january2017 we have 5 weeks. but i need to display 4 weeks only means

my week start is : Monday

so by below pic

25,26,27,28,29,30,31------ should be december 2016 week 4

29,30,31,1,2,3,4, ------------------- should be in week1 of feb

Re: RE:MonthEnd

rogerioqv — Tue, 21 Feb 2017 18:04:13 GMT

Friend, have you tried the weekyear function?

Re: RE:MonthEnd

Anil_Babu_Samineni — Wed, 22 Feb 2017 01:47:11 GMT

Chanty, Can you create dummy data please which illustrate the output. We are happy with Random dates too

Anyway, look here Recipe for a 4-4-5 Calendar

Re: RE:MonthEnd

Chanty4u — Wed, 22 Feb 2017 05:26:46 GMT

No its not working

Re: RE:MonthEnd

adamdavi3s — Wed, 22 Feb 2017 09:46:08 GMT

Something like

if( month(weekend(date) > month(weekstart(date),month(weekend(date) ,month(weekstart(date) ) as month?

Depends on what logic you want really, but the 4-4-5 as mentioned above will probably do it

Re: RE:MonthEnd

settu_periasamy — Wed, 22 Feb 2017 09:47:29 GMT

Hi sureshqv,

Can you create a excel file , what do you have and the expected output in other column?

Re: RE:MonthEnd

hic — Wed, 22 Feb 2017 10:08:15 GMT

You need a Broadcast calendar. See Calendars

HIC

Re: RE:MonthEnd

Chanty4u — Wed, 22 Feb 2017 13:32:48 GMT

thanks Henric.. i tried that script still its not working .it is not giving as i mentioned

Re: RE:MonthEnd

Chanty4u — Thu, 23 Feb 2017 05:27:43 GMT

Hi Setu,

PFA. !

Re: RE:MonthEnd

ahaahaaha — Mon, 27 Feb 2017 14:24:35 GMT

Hi,

I changed the initial settings at the top of the script. The value of FirstWeekDay with 0 changed to 6

SET FirstWeekDay=6;

Script code

Dates:

LOAD

Date(Date('18.12.2016') + RecNo() - 1) as Period

autogenerate(Date(Today()) - Date('18.12.2016') + 1);

autoCalendar:

LOAD

Period,

Date(Period) as %Date,

WeekDay(Period) as DayWeek,

Week(Period) as NumberWeek

Resident Dates;

DROP Table Dates;

Result

It should be noted that I have Russian localization of QlikView and day of the week starts Monday, not Sunday. If this is the correct decision may be changed by one setting FirstWeekDay.

*********************************************************************************************************************************

You can even offer such variant with the start numeration weeks from the beginning of the year

SET FirstWeekDay=0;

Dates:

LOAD

Date(Date('18.12.2016') + RecNo() - 1) as Period

autogenerate(Date('31.12.2016') - Date('18.12.2016') + 1);

autoCalendar:

LOAD

Period,

Date(Period) as %Date,

WeekDay(Period) as DayWeek,

Week(Period) as NumberWeek

Resident Dates;

DROP Table Dates;

SET FirstWeekDay=6;

Dates:

LOAD

Date(Date('01.01.2017') + RecNo() - 1) as Period

autogenerate(Date(Today()) - Date('18.12.2016') + 1);

Concatenate(autoCalendar)

LOAD

Period,

Date(Period) as %Date,

WeekDay(Period) as DayWeek,

Week(Period) as NumberWeek

Resident Dates;

DROP Table Dates;

Regards,

Andrey

Re: RE:MonthEnd

anjali0108 — Tue, 28 Feb 2017 12:21:53 GMT

Hi.

Please use the below code and tell me if it works:

First in edit script:

write:

SET DayNames='Mon;Tue;Wed;Thu;Fri;Sat;Sun;'

SET FirstWeekDay=0;

then write the below script in ur code:

Common:

load *,

MonthName(CommonDate) & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth_MonthlyReports;

load *,

MonthName(MakeWeekDate(Year(CommonDate), week(CommonDate))) & '- Week' & ' ' & WeekNumberInMonth as WeekNoAndMonth_WeeklyReports;

load *,

if((week(CommonDate) - week(monthstart(CommonDate)) + 1)<1,1,(week(CommonDate) - week(monthstart(CommonDate)) + 1)) as WeekNumberInMonth;

LOAD *,

MakeDate(CommonYear,CommonMonth,CommonDays) as CommonDate

FROM

$(AppQvdPath)Common.qvd

(qvd);

after this use

dimension as: WeekNoAndMonth_MonthlyReports ..your weeks will start forming from monday ....

please revert if it wrks

Re: RE:MonthEnd

Chanty4u — Fri, 03 Mar 2017 06:17:26 GMT

while loading your script below error it showing ?

Re: RE:MonthEnd

ahaahaaha — Fri, 03 Mar 2017 06:31:48 GMT

Я получил все хорошие (прикрепленный файл)

Re: RE:MonthEnd

Chanty4u — Fri, 03 Mar 2017 06:35:09 GMT

но я не имею лицензии он possble, чтобы отправить его в QVF? (Qliksense)

Re: RE:MonthEnd

rahulpawarb — Fri, 03 Mar 2017 06:52:19 GMT

Hello Chanty,

Trust that you are doing great!

Load statement which is throwing an error is expecting input date in Number format. If you pass the date in number format then error with reload will be eliminated.

Please correct me if I misunderstood.

Regards!

Rahul

Re: RE:MonthEnd

ahaahaaha — Fri, 03 Mar 2017 06:56:43 GMT

You showed a picture Qlik Sense. Version Qlik Sense Desktop can open other people's files.

Opening an app in Qlik Sense Desktop ‒ Qlik Sense‌

I usually just drag the file QVF to your desktop Qlik Sense (attached file).