topic Re: Week Calculation 2 in QlikView

Week Calculation 2

Thu, 11 Dec 2014 16:25:46 GMT

Hi All

I have a calculation by week number which returns Week1 to Week4 or Week5 for every month.

Calculation starts from Monday to Sunday and when a month ends during the week, it show up to the sunday which is in the next month.

Day(WeekStart("RE_DAY"))&'-'&Day(WeekEnd("RE_DAY"))&' Week'& if((week("RE_DAY") - week(monthstart("RE_DAY")) + 1)<1,1,(week("RE_DAY") - week(monthstart("RE_DAY")) + 1)) as Weeks

I would like Week5 to be 27-31 then November to be 1-2 Week1, 3-9 Week2...etc

How can I do this?

Re: Week Calculation 2

arthur_dom — Thu, 11 Dec 2014 16:44:57 GMT

I did a quick brainstorm. Please refer the attached file.

On script I created a simple if to test and choose day to be selected.

Script:

if( month(WeekStart( Field_Date) )<> month( Field_Date), 1 , day(WeekStart( Field_Date) ))

&' - '&

if( month(WeekEnd( Field_Date) )<> month( Field_Date), day(Field_MonthEnd) , day(WeekEnd( Field_Date) ))

& ' - ' &

Field_Month AS

Field_Week_In_Month

Regards,

Re: Week Calculation 2

Thu, 11 Dec 2014 17:18:33 GMT

Wow!!! it works perfectly on your app. Just wondering how I'm going to apply this to my app. I have a field called "RE_DAY" wich contains the dates calculated. Should I recreate the entire script you wrote in my app then replace the dates with the date field?

Re: Week Calculation 2

arthur_dom — Thu, 11 Dec 2014 17:27:57 GMT

I believe you don't need to recreate the entire script only the Calendar.

But if you are using only the RE_DAY (and if it is a date field) try this

if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))

&' - '&

if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) ))

& ' - ' &

month(RE_DAY) AS

Field_Week_In_Month

Re: Week Calculation 2

Thu, 11 Dec 2014 17:40:17 GMT

Dude you are awesome!!! I would just like the dates to show as 29 - 30 - Sep to be 29 - 30 Week5 without breaking the function.

Is it possible?

Re: Week Calculation 2

Thu, 11 Dec 2014 18:01:27 GMT

Never mind...I got it right

If( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))&' - '&
if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) )) &' Week'& if((week("RE_DAY") - week(monthstart("RE_DAY")) + 1)<1,1,(week("RE_DAY") - week(monthstart("RE_DAY")) + 1)) AS Weeks

Thanks so much for your asistance!

Re: Week Calculation 2

Thu, 11 Dec 2014 18:45:19 GMT

Oh... actually it doesnt work very well . It's duplicating some week numbers

Any suggection on how I can fix this?

Re: Week Calculation 2

arthur_dom — Thu, 11 Dec 2014 19:07:06 GMT

My expression to that is slightly different:

if( month(WeekStart( Field_Date) )<> month( Field_Date), 1 , day(WeekStart( Field_Date) ))

&' - '&

if( month(WeekEnd( Field_Date) )<> month( Field_Date), day(Field_MonthEnd) , day(WeekEnd( Field_Date) ))

& ' - Week ' &

( week(WeekStart(Field_Date)) - week(MonthStart(Field_Date))+1) AS Field_Week_Num_In_Month

and on April 2012 its returning 6 Weeks -> It started on a Sunday and ended in a Monday - if your week start is on Mondays it will return 6 weeks

Re: Week Calculation 2

Fri, 12 Dec 2014 05:43:35 GMT

Thanks! one last thing....how do I sort my list box to be like your? (from Week1 to the last Week). Mine is currently not sorted that way

Re: Week Calculation 2

Fri, 12 Dec 2014 05:51:48 GMT

And that Weeks numbering is inconsistant. Check this...

This is the full script for the list box

if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))
&' - '& if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) ))
&' Week'& (week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) AS Weeks

Re: Week Calculation 2

arthur_dom — Fri, 12 Dec 2014 12:17:51 GMT

About the numbering, you can try using the min function... but it only works with a year and a month Selected .

About the week - 47 it happens on December and janaury.. i checked again... please use formula below .

if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) ))

&' - '&

if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(monthend(RE_DAY)) , day(WeekEnd( RE_DAY) ))

& ' - Week ' &

IF( ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) < 0, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1)+52, ( week(WeekStart(RE_DAY)) - week(MonthStart(RE_DAY))+1) )

Re: Week Calculation 2

Fri, 12 Dec 2014 19:46:04 GMT

Thanks Arthur! It works fine now