Date issue - how to add years

[#1]

Hello QlikView community,

Firstly, I'm pulling data into QlikView with dates like 17-Feb, 4-Mar, 12-Apr etc with no Year.

Now I know the Year for those dates were 2015, how can I add years to those dates?

Secondly, all future data sheets are going to have the same date format, is there a way that I can import the data and years gets assigned automatically? What are my options? I'd like to make it as dynamic as possible.

Dustin

[MK_QSL]

Use below in script.

Date#(Date#(YourDate,'DD-MMM') &'-' & (Year(Today())-1),'DD-MMM-YYYY') as YourDate

But how you will know that Next 17-Feb is 2016 ?

[sunny_talwar]

Or even this:

Date#(YourDate & '-' & (Year(Today()) - 1), 'DD-MMM-YYYY') as YourDate

[#4]

Hi Sunny,

Date#(YourDate & '-' & (Year(Today()) - 1), 'DD-MMM-YYYY') as YourDate works,

But the dates don't pick up as a date, it picks up as a text value. Can I fix this?

Also what you saying "But how you will know that next 17-Feb is 2016"?  That's part of the question. Is this not possible?

Thanks for the reply!

[sunny_talwar]

Really? Date#() function is used to convert a text field into a actual date. Are you sure it is text? Have you tried creating a chart with YourDate as dimension and Num(YourDate) as expression and see if you can see the numeric representation of dates? or do you see null?

For the second part, are you saying that when after 17-Feb you will need to use 2016? and before you will be using 2015? For all dates or only the dates after 17-Feb?

[#6]

Hi Sunny,

I have not tried that, I'm away from my computer today, but will try it tonight and report back.

For the second part to clarify:

• All the data in the model currently is from February - December 2015
• The date format of the data is 'DD MMM' with no yearly values
• I want to add years to the dates
• I will be bringing in new data once I get this right, but the new data should show 2016 since the data is from January 2016 (this year)

I would like to make this as dynamic as possible.

Hope that helps.

[sunny_talwar]

For second query, the only way I think it could work is by incrementally storing your data in a QVD with the year information in it.

[sathishkumar_go]

Hi Dustin,

1) While pulling a data from qlikview, Create a qvd like qvdname_YYYYMM format (use year(today()) function to get the year.

2) Create a master calendar Master Calendar Generation Script

3) link master calendar in your data model

-Sathish