Master Calendar when only a year and week number is available

[#1]

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

[fkeuroglian]

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

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

Fernando

[#3]

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!

[maxgro]

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]

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

[MarcoWedel]

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;

regards

Marco

[#7]

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