Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
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
Gysbert_Wassenaar

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
Author

It worked, thank you!

Sharad02
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