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

Date issue - how to add years

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

7 Replies
MK_QSL
MVP
MVP

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

Not applicable
Author

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?

Not applicable
Author

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
Partner - Specialist
Partner - Specialist

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