topic Re: Script function Weekname returns a monthname in QlikView

Script function Weekname returns a monthname

skovsgaard — Mon, 26 Nov 2012 14:06:53 GMT

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

Re: Script function Weekname returns a monthname

swuehl — Mon, 26 Nov 2012 14:33:27 GMT

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.

Re: Script function Weekname returns a monthname

skovsgaard — Wed, 28 Nov 2012 13:34:38 GMT

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.

Re: Script function Weekname returns a monthname

swuehl — Wed, 28 Nov 2012 13:39:22 GMT

... 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.