Script function Weekname returns a monthname

[skovsgaard]

Hi

I have data numbered 1 to 12 for months and 1 to 52 for weeks, and I am using Monthname and Weekname to convert it into Month and Weeks. However when doing so Week 40 2012 returns Oct 2012. See below script for example.

InlineLoad:
Load * inline [MonthNo, Year, Classification
9, 2012, Month
39, 2012, Week
10, 2012, Month
40, 2012, Week
11, 2012, Month
41, 2012, Week
];

Convert:
Load
*,
if(Classification = 'Month', monthname(MakeDate(Year,MonthNo,1)), Weekname(MakeWeekDate(Year,MonthNo,6)) ) as Period,
if(Classification = 'Month', 'A', Weekname(MakeWeekDate(Year,MonthNo,6)) ) as Period1
Resident InlineLoad;

Drop table InlineLoad;

For week 40, Period returns "Oct 2012" but if I dont convert to Months as in Period1 it correctly returns "2012/40"

Can anyone explain why, or is this a bug?

Regards

Soren

[swuehl]

Both Week 40 and Month 10 share the same numeric value in your sample, 41183 or 2012-10-01 as ISO Date.

I believe that's the issue here, you want to use two different text representations for the same numeric value.

[skovsgaard]

Yes, after some further testing this was also my conclusion, and eventhough the make date of week 40 is made for 6th day of the week, the Weekname function transfers it into the same timestamp as the month. My first assumption was that it would only convert the part where the classification was true, but as soon as the timestamp have been converted,it sticks eventhough the same timestamp may appear as week as well.

Solution was to make a text representation as well, a bit more complex but it works.

[swuehl]

... eventhough the make date of week 40 is made for 6th day of the week, the Weekname function transfers it into the same timestamp as the month.

You can also use a weekoffset argument with Weekname() function, similar to MakeWeekDate(). Please check the HELP if this is of any interest.