Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Split Date into Month and Year

In my excel spreadsheet that I'm uploading into QV I have a "date received" column where all the dates are in this format: DD-MMM-YY (e.g. 07-Mar-13). I would like to have options in my multi box in QV to see just Month and Year. I'd like to compare data from different years by month (like what was the data like this March compared to March 2012), but I don't want to have to change anything in the spreadsheet because the file is used by many people and I don't want to change the format of that "Date Received" column. Is there any way just to create a Month and Year choice in my multi box that draws the information from DD-MMM-YY in the Date Received info?

12 Replies

That sounds like you selected Override Document Settings on the Number tab and set it to Date for the month field. The month() function returns an integer between 1 and 12 (actually, it creates a dual value with also a text value). If you format 1 as a date you get the date 31-12-1899. So, if that's the case unselect Override Document Settings.

talk is cheap, supply exceeds demand
Not applicable

It worked, thank you!

Contributor II
Contributor II

Sometimes Date table might not have same alignment like for e.g in given example of Ship Date. If You try to used Year([Ship Date]) as Year it will not get Correct Output So instead of that in this situation we better to used SubField([Ship Date], '/',3) as Year_1  . 

You can see the difference from the images.Original TableOriginal Table


Split Year Updated  TableSplit Year Updated Table