topic What does MONTH function actually do? in QlikView

What does MONTH function actually do?

Fri, 01 Jun 2012 12:46:14 GMT

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

Re: What does MONTH function actually do?

swuehl — Fri, 01 Jun 2012 13:22:37 GMT

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

Re: What does MONTH function actually do?

swuehl — Fri, 01 Jun 2012 13:30:38 GMT

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

Re: What does MONTH function actually do?

john_duffy — Fri, 01 Jun 2012 13:34:14 GMT

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

;

What does MONTH function actually do?

Fri, 01 Jun 2012 13:47:04 GMT

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.

Re: What does MONTH function actually do?

swuehl — Fri, 01 Jun 2012 13:56:37 GMT

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

What does MONTH function actually do?

Fri, 01 Jun 2012 14:00:58 GMT

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

What does MONTH function actually do?

Fri, 01 Jun 2012 14:06:22 GMT

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?

Re: What does MONTH function actually do?

swuehl — Fri, 01 Jun 2012 14:07:42 GMT

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

Re: What does MONTH function actually do?

swuehl — Fri, 01 Jun 2012 14:15:06 GMT

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

What does MONTH function actually do?

Fri, 01 Jun 2012 14:56:12 GMT

Hi Stefan, I have gone through all the tutorials, but they have very little detail on scripting, and what they have focuses simply on loading from text or Excel tables, not massive SQL databases! I did create a small example that loaded from an Excel sheet instead, and it worked fine, but that's because I was only using QV functions and no SQL.(Single LOAD statement, with last two lines modified to get "Month" and "Year" from "Sale Date"). But working with mixed QV and SLQL scripts isn't covered, and I'm not sure how what happens in one part affects what happens in the other part. This is the sort of high level stuff I'm looking for but can't seem to find here. <p><p>I did, however, resolve my labelling issue. I assumed, because the "Label" box was grayed out, you couldn't select it. Surprise! As my boss showed me, all I had to do was click on the label box, and I could enter anything, so that's fixed.

What does MONTH function actually do?

swuehl — Fri, 01 Jun 2012 15:02:24 GMT

Kevin,

I assume the box itself is not greyed out, but the text <use field name> is written in a greyish color?

Well, that might be confusing.

This forum is a great place to find information about almost anything. Don't be shy hacking everything you need to know into the search bar (upper right), e.g. enter SQL LOAD, then you find as one of the first listed threads:

http://community.qlik.com/thread/13682