topic Re: Re: Calendar Weeks in QlikView

Calendar Weeks

Thu, 23 Oct 2014 20:05:14 GMT

Hello,

I have a calendar that shows me Week numbers, is it possible instead of Week Number to show the actual days?

For example, current week is 43, I would like to show Oct 20-26, 2014 instead and when the week overlaps another month like week 44, then I would like to show Oct 27-Nov 01, 2014. How can I achieve this on my calendar?

Thank you,

Hugo

Re: Calendar Weeks

Anonymous — Thu, 23 Oct 2014 22:54:31 GMT

Belcils,

You can try it!

WeekStart(Date)

output 20/10/2014

WeekEnd(Date)

output 26/10/2014

You can select week 43

WeekStart(Date) &'-'& WeekEnd(Date)

You can select week 43

20/10/2014 - 26/10/2014

Re: Calendar Weeks

MarcoWedel — Thu, 23 Oct 2014 23:41:18 GMT

Hi,

one solution could be:

tabCalendar:
LOAD *,
    Week(Date) as Week,
    WeekName(Date) as WeekName,
    Dual(Date(WeekStart(Date),'MMM DD')&'-'&Date(WeekEnd(Date),If(Month(WeekStart(Date))= Month(WeekEnd(Date)),'DD','MMM DD')),WeekStart(Date)) as WeekStartEnd,
    WeekYear(Date) as WeekYear;
LOAD Date(YearStart(Today())+IterNo()-1) as Date
AutoGenerate 1
While IterNo() <= DayNumberOfYear(Today());

hope this helps

regards

Marco

Re: Re: Calendar Weeks

MarcoWedel — Thu, 23 Oct 2014 23:44:40 GMT

forgot the year ...

tabCalendar:
LOAD *,
    Week(Date) as Week,
    WeekName(Date) as WeekName,
    Dual(Date(WeekStart(Date),'MMM DD')&'-'&Date(WeekEnd(Date),If(Month(WeekStart(Date))= Month(WeekEnd(Date)),'DD','MMM DD'))&', '&WeekYear(Date),WeekStart(Date)) as WeekStartEnd,
    WeekYear(Date) as WeekYear;
LOAD Date(YearStart(Today())+IterNo()-1) as Date
AutoGenerate 1
While IterNo() <= DayNumberOfYear(Today());

hope this helps

regards

Marco

Re: Re: Calendar Weeks

Fri, 24 Oct 2014 01:49:32 GMT

Thank you very much for your responses, Marco your solution works 100% and it solves my issue. Thank you very much for your help.

Re: Calendar Weeks

MarcoWedel — Fri, 24 Oct 2014 06:22:05 GMT

You're welcome.

Regards

Marco

Re: Re: Re: Calendar Weeks

MarcoWedel — Fri, 24 Oct 2014 21:38:53 GMT

Hi,

to correct different years for week start and week end between december and january, another solution could be:

tabCalendar:
LOAD *,
    Week(Date) as Week,
    WeekName(Date) as WeekName,
    Dual(Date(WeekStart(Date),'MMM DD'&If(Year(WeekStart(Date))<>Year(WeekEnd(Date)),', YYYY'))&'-'&
          Date(WeekEnd(Date),If(Month(WeekStart(Date))<>Month(WeekEnd(Date)),'MMM ')&'DD, YYYY'),
          WeekStart(Date)) as WeekStartEnd,
    WeekYear(Date) as WeekYear;
LOAD Date(AddYears(YearStart(Today()),-5)+IterNo()-1) as Date
AutoGenerate 1
While AddYears(YearStart(Today()),-5)+IterNo()-1 <= Today();

hope this helps also

regards

Marco