Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Modernize Your QlikView Deployment webinar, Nov. 3rd. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Month number to Month name

I'm looking for a way to convert Month numbers to their three-letter names.

Currently, the Month field is loaded by Month(OrderDate) where OrderDate is a Date. However, inside QV, the months show up as numbers from 1-12 and I need them to say Jan, Feb, ...

So far on the forums I've only seen how to convert from the Names to the numbers but not back. Is there a way to do this?

Thanks

26 Replies
Highlighted
Champion III
Champion III

Yiling,
Now I understand even less. You're saying that you "used Month(OrderDate) to get the month values from OrderDate and stored them into the newly made Month field". But in this case your Month field is dual, that is the number and the text at the same time. The only thing you need to do is to check what is the Number properties of your list box.
See the atched Calendar example. There are two list boxes named Month on the right. They are the same Month field, th difference is only in Number properties.
If it doesn't help, could you please post your app (ot a part of it) to see waht's going on there.

Highlighted
Not applicable

Try using the "PICK" function.

PICK(month(OrderDate),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

The "PICK" function will convert 1 to Jan., 2 to Feb, etc.

rob

Highlighted
Champion III
Champion III

Rob,
Pick is fine, but isn't it simpler to use just month(OrderDate)? Or at least text(month(OrderDate))...

Highlighted
Not applicable

I think that might be part of the problem. My Month field isn't dual. In your example, the Month ListBox displays the names by default. It only shows the numbers when you click Override Document Settings under the Number properties and click on Integer. However, mine only shows the number.

Highlighted
Not applicable

I think there is quite a difference between the Month() function in Qlikview and the Month() function in SQL. If I directly use the function Month(OrderDate) as an expression and set that as the dimension, Months show up with names and can be changed to numbers. However, I think the Month() when I did Select *, Month(OrderDate) as Month in my load script only takes numbers?

I really don't know SQL so I may be wrong but this is just what I'm observing happen.

Highlighted
Not applicable

Eugene,

I think my problem is most similiar to your example. Your Month field as shown in your tablebox is also numbers. I don't need any chart or any object to display the Month names based on the numbers. I need the numbers to actually be the monthnames.

So basically, if you could make your table box with the Month field show Jan, Feb, Mar, etc, then it would solve my problem too.

Yiling

Highlighted
Champion III
Champion III

The problem is that you're trying to use QV functions in SQL. It doesn't wotk this way. (Well, i should've noticed in your example). You can use applymap, or pick, or any other QV function on the QV LOAD level. Even if you use the same month() as now but on the LOAD, it will work just fine.
LOAD
*,
month(OrderDate) as Month;

SQL SELECT
...

View solution in original post

Highlighted
Not applicable

Wow. Such a simple fix. Thank you.

Highlighted
Champion III
Champion III

We should've resolved it long ago. I simply didn't notice that the month() function was in SQL... Tongue Tied

Highlighted
Partner
Partner

I have tried: PICK(month(OrderDate),'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

The problem is if the OrderDate isn't actually a date but a number (1 = Jan, 2 = Feb etc).

So, if you remove the month(OrderDate) it works OK.

I used: PICK(OrderDate,'Jan','Feb','Mar','Apr','May','Jun','Jul','Aug','Sep','Oct','Nov','Dec')

In order to sort this, if you are using a List Box, you need to sort by an expression equal to the numeric number