Split Date into Month and Year

[#1]

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?

[Gysbert_Wassenaar]

Create Year and Month fields in the script

Load

    ...myfields...,

    Year(MyDate) as Year,

    Month(MyDate) as Month

from ...myexcelfile...;

[#3]

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

[Gysbert_Wassenaar]

Replace MyDate with [Date Received]

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

[#5]

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.

[Gysbert_Wassenaar]

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?

[#7]

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)."

[Gysbert_Wassenaar]

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.

[#9]

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.

[kaushiknsolanki]

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