What does MONTH function actually do?

[#1]

I have the following line in my script:

Month(Coalesce(OM.ClosedDate,'1/1/1989')) As "Month",

Now, the reference manual says:

month(date)   Month. Returns a text string representing the month when the fraction of expr is interpreted as a date, but can be formatted as a number.   Example:  month( '1971-10-30' ) returns Oct.

But when I run the script, the field "Month" is filled with "1,2,3,4..", and not text names. This makes reports harder to read.

I've asked this question a couple of times, and gotten well-meaning but non-helpful replies, such as use Load Inline.. constructs in the script (did that, Month still reports as number, not text. So, let me rephrase more explicitly, with emphasis:

How do I convert the Month number to the text name IN THE TABLES, NOT IN THE SCRIPT?

And since I'm a newbie, please don't just post a formula, but tell me where to put it (i.e. what Properties tab, what formula, etc.

I'm using month as a dimension in a sales report - the only mathematics is sum(Sales).

[swuehl]

month(date)  

Month. Returns a text string representing the month when the fraction of expr is interpreted as a date, but can be formatted as a number.   Example:  month( '1971-10-30' ) returns Oct.

Well, that's again a nice example of a bad maintained Help text and showing the disadvantages of allowing the use of copy and paste to the technical writers It's hard to understand, even for me.

expr is not defined in that Helps topics context, it was used in the explanation of hour(), minute() and second().

Replace expr with the argument passed to the function month() [In the Help, the argument alias used is date, which might lead to further confusion]

Also, month() is not looking at the fraction of the argument, but on the integer part...

If someone would offer me a job as QT QA, I would accept, must be a lazy job....

Ok, let me try to explain month() function to you

month() takes one argument, an expression that QV must be able to interprete as number.

A date type has a numerical representation within QV, so we can pass a QV date type field or a an expression / function that returns a date (and that's I would call the 'normal' use, passing a date).

Example

Month( today() )

today() returns today's date, this is the argument to month() function.

If you pass any other numerical, QV will interprete it as date, the integer part is telling the actual date, the fractional part the time of day.

Month() now will return a dual value, that's a special value that holds two representations, a textual and a numerical. Please refer to the Help or manual, looking for dual() function.

In the numerical representation, QV will put in the month number, ranging from 1-12. In the textual representation, QV will put in the Month name abbreviation that you defined in your OS or using the standard format setting in the script:

SET MonthNames='Jan;Feb;Mar;Apr;May;Jun;Jul;Aug;Sep;Oct;Nov;Dec';

So, for our example Month( today() ), QV will use return a dual with

6 as numerical representation, and 'Jun' as textual representation.

You can force QV at anytime to return the textual part of a dual using text(dualvalue), e.g. text(month( today() )) will return 'Jun', and using num(dualvalue), e.g. num(month(today())) will return 6.

If you don't specify the representation you want returned, QV will use it's internal logic. I believe, if you use

month( date ) as dimension, it will use the textual representation by default (but maybe you have changed that default in document settings or somewhere else?).

So I can't answer your question why you don't get the text in your dimension values (but you can try to use text(Month) as dimension, as mentioned.

If possible, post a small sample app, so someone can have a look what is going on in your app.

Hope this helps,

Stefan

[swuehl]

coalesce is a (Transact-)SQL statement, not a QV function.

Are you using month in a  LOAD or SELECT context?

You can't use Month() as described above in the SELECT part, here you will use the SQL function...

But you can use the QV Month() function in a preceding load:

LOAD

Date,

Month(Date) as Month;

SELECT Coalesce(OM.ClosedDate,'1/1/1989') as Date FROM ...

[john_duffy]

Hello.

Are you applying the Month function on your SQL statement?

If so, you need to change the statement to apply the QV Month function on your date, not the DB2 Month function.

Something like this:

Load

        ClosedDate,

        Month(ClosedDate) as ClosedDate_Month

        ;

SQL SELECT

        COALESCE(ClosedDate,'1/1/1989')) as ClosedDate

FROM YourTable

     ;

[#5]

Thanks for your help; some of it works but I'm still having troubles. :{

First, I've been using QV less than a week, so bear with me if my questions seem stupid. I added to my Sales by Month (which is a pivot table)  in Properties-Dimensions-Add Calculated Dimension the calculated dimension "=text(month(date))". This works fine in my Pivot Table (i.e. months now show as "Mar", "Apr", etc.), but I have two selection boxes in the sheet, a ListBox "Month", and an unnamed slider where the user is supposed to make his selections. They both show month numbers instead of names, and when I went into the Listbox "Month", and entered the expression "=text(Month(Month)), my list box displays (just the first few entries, don't know how to paste images):

1 -

2 -

3 Jan

4 -

Where the selected month is March, not January, and the report shows (correctly) March's data.

So, a few questions: should I change my "Month" box from a Listbox to some other type? Is there some other issue I'm missing?

And a final, minor, point - in my "Sales by Month" chart, the "Month" dimension now shows as the calculation (i.e. '=text(month(date))') Is there a way to put a label on this so that it simply shows "Month"?

Thanks again for your help.

[swuehl]

>Is there a way to put a label on this so that it simply shows "Month"?

There should be an entry box on dimension tab (under the label check box)  that by default says <use field name>.

Put in:    Month

But I think you should solve your underlying problem instead of using text(month(date)) here and there.

Are you using more than one Month field (field named Month in different tables) in your QV data model?

Again, could you post a small sample app? Maybe with scrambled / mock up data if needed?

[#7]

This is a reply to both Stefan and John:

As I said, I'm quite new to QV. My background is mostly procedural languages, like VB, Fortran, etc., although I've done some SQL queries in the past.

The QV documentation I've been able to find here (tutorial, ref manual) is singularly unhelpful in differentiating between scripting, and table expressions which I, rightly or wrongly, consider two different things. To my poor, naive, procedural mind, doing something like "LOAD ClosedAmount.." before I've even told the program what or where ClosedAmount is defined makes no sense. Is there something that explains what the LOAD process actually does?

I hate experimenting with this stuff, because the file contains literally millions of records, and each reload takes about 4 minutes and irks my co-workers! So I'd prefer a better understanding to start before throwing stuff at the machien and seeing what happens. Any links/explanations will be gratefully received.

Finally, in a completely non-judgemental way, I find it confusing that QV allows user defined fieldnames to have exactly the same name as various built-in functions, instead of having reserved keywords, but maybe that's just me. :}

[#8]

There should be an entry box on dimension tab (under the label check box)  that by default says <use field name>.

There is, but it is greyed out (i.e. non selectable) for every field, even those that aren't calculated. I have seen that option on other tables, and used it, but it is always non selectable for a calculated dimension. Is there a sheet/document setting that I missed?

[swuehl]

I assumed up to now that you stepped already through the free e-learning and offline tutorial files, have you?

I wouldn't recommend to start loading from a large database, start with something really simple (as the tutorial does).

If you need to do so, there are some methods limiting the amount of rows returned (debug mode in script editor, first prefix to load statement, where clause...)

If you are looking for link for Newbies, there is a whole section here in the community:

http://community.qlik.com/community/new-to-qlikview

[swuehl]

>and entered the expression "=text(Month(Month)), my list box displays (just the first few entries, don't know how to paste images):1 -2 -3 Jan4 -

Your Month field values are no dates, but duals and have a numerical repesentation (assuming you have created your Month field from a date using the QV month() function in a LOAD context).

3 is representing Jan 2nd 1900 (so you can guess what 1 is representing). That's why you get Jan returned. But I can't explain why you don't get Month names for the other list box lines.