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

Week Calculation 2

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

2014-12-11_18-16-23.png

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

How can I do this?

1 Solution

Accepted Solutions
arthur_dom
Creator III
Creator III

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

View solution in original post

11 Replies
arthur_dom
Creator III
Creator III

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,

Not applicable
Author

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?

arthur_dom
Creator III
Creator III

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

Not applicable
Author

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?

Not applicable
Author

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!

Not applicable
Author

Oh... actually it doesnt work very well . It's duplicating some week numbers2014-12-11_20-40-14.png

Any suggection on how I can fix this?

arthur_dom
Creator III
Creator III

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

Screen Shot 2014-12-11 at 17.06.00.png

Not applicable
Author

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

Not applicable
Author

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

2014-12-12_07-47-54.png

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