find week number from month and year

[neha_sri]

Hi,

I have Month and year column with me,Can I find week from available month and year,,

Thanks in anticipation

[JonnyPoole]

you can. you would use makedate() or date#  to create an expression that combines month and year into a single field, and then use the week() function to extract the week from the results of makedate() or date# function.

but the exact syntax will depend on your data that holds the month and year.  Can you share an example ?

[MK_QSL]

You can get but may be the max week number and min week number for that particular month and year...

[neha_sri]

Yes...But I want all the weeks falling in that month

[MK_QSL]

T1:

Load

  MakeDate(2014,Month,1) as MonthStart,

  MonthEnd(MakeDate(2014,Month,1)) as MonthEnd,

  Month,

  2014 as Year

Inline

[

  Month

  1

  2

  3

  4

  5

  6

  7

  8

  9

  10

  11

  12

];

NoConcatenate

Load

  *,

  Week(Date) as Week;

Load

  MonthStart,

  Date(MonthStart + IterNo() + 1) as Date,

  MonthEnd,

  Month,

  Year

Resident T1

While MonthStart + IterNo() - 1 <= MonthEnd;

Drop Table T1;

===================

Replace 2014 with Year in your script...

[MarcoWedel]

Hi,

maybe you could use a calendar table created by the script to precalculate all possible year/month/week name combinations during the relevant time period.

e.g. like this:

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,
    QuarterName(Date) as QuarterName,
    Year(Date) as Year,
    WeekYear(Date) as WeekYear;
LOAD Date(Date#('01/01/2005', 'MM/DD/YYYY')+IterNo()-1) as Date
AutoGenerate 1
While Date#('01/01/2005', 'MM/DD/YYYY')+IterNo()-1 <= Today();

hope this helps

regards

Marco