Variable for Month

[NickBentley]

Hi All,

I am trying to create a variable for shipping month which at times is different from the actual calendar month - i.e. if the first few days of April like the 1st and 2nd fall on a Thursday and Friday I still want these dates to be classed in the month of March. 

In my expressions, I am using a  vCurrentMonth variable which is built from month(today()) but this will give the wrong value in the instance above when the shipping month is different from the calendar month. 

Any suggestions would be greatly appreciated. 

Thanks,

Nick

[edwin]

you need to define the business rules.  if the 1st of the month falls on a Tuesday is that counted towards  the prior month?  maybe your determining factor is when the 1st day of the week falls?  or is it based on the number of days in the week - whichever is greater?  either way you can use the week  to test if the calendar date falls under the current month or prior month.

this sample assumes that any calendar date falling on the same week where sunday is prior month will be counted towards the prior month:

dates:
load date, week(date) as week, if(weekday(date)='Sun',MonthStart(date)) as tmpSHIPMONTH, year(date) & '|' & Week(date) as Yearweek, weekday(date) as weekday, day(date) as day;
load date(today()-iterno()) as date while iterno()<300;
load 1 autogenerate(1);

NoConcatenate
weeks:
load distinct tmpSHIPMONTH as SHIPMONTH, Yearweek 
Resident dates where not isnull(tmpSHIPMONTH);

inner join (dates)
load * resident weeks;

drop table weeks;
drop field tmpSHIPMONTH;

[edwin]

as you can see dec 1 to 5 fall under nov bec they are on the same week as the last of Nov which included the sunday for that week