Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Master Calendar when only a year and week number is available

Hi!

I'm wondering if it is possible to create a master calendar from a table that only has years and week numbers in it? Or any way else get the months, quarters etc out? All the tutorials I've watched already have a proper date to use as a starting point for the calendar.

I've also tried the Monthname(MakeWeekDate(Year, Week)) but it is not working for me.

Can anyone help me out with this one?

Regards,

Heidi

6 Replies
fkeuroglian
Honored Contributor

Re: Master Calendar when only a year and week number is available

Hi, what is the result of your field that you want?

of course you can, what information do you want in the calendar

Fernando

Not applicable

Re: Master Calendar when only a year and week number is available

I need to get year-month and year-quarter from the weeks. Best would be if I could form actual dates for the weeks. Like week 23 in 2014 is 2nd of june 2014 and so on.

Thanks for the help!

MVP
MVP

Re: Master Calendar when only a year and week number is available

if you have year and week I think you can only use MakeWeekDate

MakeWeekDate(2014,25)


and then create the other calendar fields as usual

year(MakeWeekDate.....)

month(MakeWeekDate.....)

and all calendar fields you want


the problem could be MakeWeekDate without the third parameter:

If no day-of-week is stated, 0 (Monday) is assumed. 

You always get monday

kedar_dandekar
Contributor

Re: Re: Master Calendar when only a year and week number is available

Hi,

I tried with the MakeWeekDate function at it worked for me..

Please find attached a sample app., I have used an inline table to represent a sample table with Year Week fields,

you can use your table in its place and give it a try..

HTH,

KD

Re: Master Calendar when only a year and week number is available

Hi,

maybe this helps:

//Load some random test data

tabData:

LOAD *,

    Ceil(Rand()*100) as fact1,

    Ceil(Rand()*100) as fact2

Inline [

year, week

2013, 23

2013, 10

2013, 19

2013, 38

2013, 3

2013, 20

2013, 12

2014, 23

2014, 12

2014, 19

2014, 3

2014, 21

];

//create master calendar

tabCalendar:

LOAD *,

    Day(Date) as Day,

    WeekDay(Date) as WeekDay,

    Week(Date) as Week,

    WeekName(Date) as WeekName,

    Month(Date) as Month,

    MonthName(Date) as MonthName,

    Ceil(Month(Date)/3) as Quarter,

    QuarterName(Date) as QuarterName,

    Year(Date) as Year,

    WeekYear(Date) as WeekYear;  

LOAD Date(MinDate+IterNo()-1) as Date

While MinDate+IterNo()-1<=Today();

LOAD Min(MakeWeekDate(year, week)) as MinDate

Resident tabData;

//Link data table to calendar

Left Join (tabData)

LOAD Distinct

  year,

  week,

  WeekName(MakeWeekDate(year, week)) as WeekName

Resident tabData;

DROP Fields year, week From tabData;

QlikCommunity_Thread_134157_Pic1.JPG.jpg

QlikCommunity_Thread_134157_Pic2.JPG.jpg

QlikCommunity_Thread_134157_Pic3.JPG.jpg

regards

Marco

Not applicable

Re: Master Calendar when only a year and week number is available

Hi,

the problem turned out to be that QV read the week numbers to be text instead of numbers because of the form of the source data. After I turned them to numbers the normal MakeWeekDate worked just fine.

But thanks for your assistance!

Regards,

Heidi

Community Browser