topic Re: Split Date into Month and Year in QlikView

Split Date into Month and Year

Thu, 07 Mar 2013 19:39:16 GMT

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?

Re: Split Date into Month and Year

Gysbert_Wassenaar — Thu, 07 Mar 2013 19:47:41 GMT

Create Year and Month fields in the script

Load

...myfields...,

Year(MyDate) as Year,

Month(MyDate) as Month

from ...myexcelfile...;

Re: Split Date into Month and Year

Thu, 07 Mar 2013 19:52:47 GMT

What do you mean (MyDate). What should I be typing there? Shoud it be ([Date Received])? Or shoud it be writing an actual date?

Re: Split Date into Month and Year

Gysbert_Wassenaar — Thu, 07 Mar 2013 19:53:54 GMT

Replace MyDate with [Date Received]

please don't ask me what you should replace ...myfields... and ...myexcelfile... with.

Re: Split Date into Month and Year

Thu, 07 Mar 2013 20:07:50 GMT

First of all thank you for your help. Second of all your script suggestion didn't even accomplish what I wanted it to do, so either you misunderstood what I needed help with, or you don't quite understand QlikView as much as you think you do. And lastly, I've only been using QlikView for a month. I don't see why you're on this site being rude to people who are just looking for some help. You're not required to respond to me at all.

Re: Split Date into Month and Year

Gysbert_Wassenaar — Thu, 07 Mar 2013 20:20:15 GMT

My apologies. That wasn't intended to insult you. The joke is on me I suppose.

The way I read your question is that you have a date field and want to create a year and a month field from that to be able to make selections of year and/or month (and use the fields as dimensions in charts). If my understanding is incorrect, please tell me what you need.

In case you do want to create new year and month fields...

I was hoping your Date Received would be a date field since it comes from an excel file. Perhaps it's a string instead. In that case you need to make a date from it. Try: Year(date#([Date Received],'DD-MMM-YYYY') as Year

Or is your field name 'date received' instead of 'Date Received'? Qlikview is case sensitive for almost everything.

If that doesn't work, could you post a representative sample of your excel file?

Re: Split Date into Month and Year

Thu, 07 Mar 2013 20:39:21 GMT

Thank you - I can't upload all of the data, but I did upload a spreadsheet with just the Date Received column. And yes, this is correct, "The way I read your question is that you have a date field and want to create a year and a month field from that to be able to make selections of year and/or month (and use the fields as dimensions in charts)."

Re: Split Date into Month and Year

Gysbert_Wassenaar — Thu, 07 Mar 2013 20:48:56 GMT

Mostly works. I see only one date that's not a date. See line 520 in your excel file: 25-Oct012 isn't a valid date. The rest gets loaded fine. See attached qvw.

If necessary 25-Oct012 can be made a date too, but it makes things more complicated. It's better to fix the incorrect data entry in the source system.

Re: Split Date into Month and Year

Thu, 07 Mar 2013 21:10:29 GMT

I've actually tried adding

Year([Date Received]) as Year,

Month([Date Received]) as Month

to the script before. It doesn't seem to work either. The Year comes out fine in the multibox, but when I look at the Month option in the multi box, all the options say Jan- and a year next to it. The years next to Jan are all 1989 or 1900, which aren't even years in my spreadsheet. Thank you for your help, but I'm not sure this is possible to fix without revealing my entire dataset.

Re: Split Date into Month and Year

kaushiknsolanki — Fri, 08 Mar 2013 07:20:58 GMT

Hi,

You can try this.

Month(Date(Date#([Date Received],'DD-MMM-YY'))) as Month,

Year(Date(Date#([Date Received],'DD-MMM-YY'))) as Year

Regards,

Kaushik Solanki

Re: Split Date into Month and Year

Gysbert_Wassenaar — Fri, 08 Mar 2013 08:46:43 GMT

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.

Re: Split Date into Month and Year

Fri, 08 Mar 2013 15:07:21 GMT

It worked, thank you!

Re: Split Date into Month and Year

Sharad02 — Thu, 21 Jul 2022 20:51:11 GMT

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 Table

Split Year Updated Table