topic Re: Weekly Date View in QlikView

Weekly Date View

Thu, 15 Jan 2015 10:26:54 GMT

Hi All

I have recieved assistance from the QlikView Community regarding this before, however it is not working properly.

I have a list that shows me WeekStart date - WeekEnd date (Mon to Sun) followed by the Week number of the month e.g 26 - 31 Week 5.

The problem is that some weeks are duplicating

This should Ideally only have 23 - 28 Week 5 for all the dates that fall within that week

Script:

Dual (num(if( month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( RE_DAY) )),'00')
&' - '&
Num(if( month(WeekEnd( RE_DAY) )<> month( RE_DAY), day(RE_DAY) , day(WeekEnd( RE_DAY) )),'00'),if(month(WeekStart( RE_DAY) )<> month( RE_DAY), 1 , day(WeekStart( 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)) AS Weeks;

Please assist?

Re: Weekly Date View

Anonymous — Thu, 15 Jan 2015 11:23:16 GMT

You do see 2 rows with 23-27 Week 5 and 23-28 Week 5

Your condition

if (month(Weekend(RE_DAY)<>month(RE_DAY), day(RE_DAY),...

works fine.

As the Weekend of 27th of feburary and of the 28th of feburary will be the first of march 3/1

the condition

month(Weekend(2/27) = March

month(2/27) = Feb

so March <> Feb, the day(RE_DAY) will be displayed! (27 and 28 in 2 rows)

I suppose the same will happen with weekstart when month(weekstart) and month(RE_DAY) will be in different

months!

Re: Weekly Date View

Thu, 15 Jan 2015 11:38:32 GMT

Thanks for your reply.

Is there any way I can avoid the rows to show like that and only show me one week row for all dates falling in that week?

Re: Weekly Date View

Anonymous — Thu, 15 Jan 2015 12:02:55 GMT

what do you want to Show in General?

why not displaying day(weekstart(RE_DAY) & day(Weekend(RE_DAY))

the result would be 23-1 for KW5 in 2015 or 29-3 for KW1

maybe 23.2.-1.3. or 29.12.-3.1. would be better?

Re: Weekly Date View

Colin-Albert — Thu, 15 Jan 2015 12:21:16 GMT

To check if the correct week numbers are being created, add a table box to a test sheet to view RE_DAY, and Weeks. You can also add the Year & Month fields.

This will allow you to confirm that the weeks are starting on the correct day, and to confirm the changeover from one year to another.

Re: Weekly Date View

Thu, 15 Jan 2015 12:28:54 GMT

In this case, I only want the Weeks to show as Week1 to Week4 or Week5 for every month instead of Week1 to Week52