Qliksense - date to month (very simple question)

[#1]

Hello everyone,

What i thought would be an easy task turned out to be a bit frustrating.

I'm simply trying to convert a date column to months.

I found a very easy formula .. Month(date). The formula "check" says it's ok but in the chart i use it in, it replaces the values by one single "-".

Am i missing something with the syntax or is the desktop version of the application more restrictive.

Thanks for your help on such an easy matter.

Greg

[Oleg_Troyansky]

This formula should work, when everything is OK... Two possible causes:

1. The field name "date" is misspelled - should it be capitalized, like Date ? Field names are case sensitive.

2. The field "date" is not a date but a string (depends on how it's loaded from the source data). In this case, it needs to be converted into a proper date field before the Month() function can be used.

If you could share a bit more about your data, we could possibly troubleshoot your issue.

cheers,

Oleg Troyansky

Check out my new book QlikView Your Business: An expert guide to Business Discovery with QlikView an...

[#3 · Accepted solution]

Oleg - wouldn't the wrong field name spit out an error message on the load?

Gregory - To Oleg's second point - you might first want to identify exactly how QlikSense is consuming your date field.  Let's say the format of the date field = 02_03_15.. or MM_DD_YY.  You could then 'force' QS to read as a Date with the Date# function:

Date#(Date,'MM_DD_YY')  <-- This tells QS how to read the date. You can then nest that function with other date functions in order to achieve your desired result:

Date(Date#(Date,'MM_DD_YY'),'MM/DD/YYYY') <-- Date() is a formatting function.

Month(Date#(Date,'MM_DD_YY'))

[robert_mika]

Try like this:

Month(Date#(YourField, 'DD-MM-YY'))

[#5]

Hello again guys,

Thank you for your quick replies, you were all right, qliksense didn't see the date as a real date, so the date# function solved this.

Thanks for your time and answers,

Have a nice day,

Gregory