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: 
Anonymous
Not applicable

Creating Date Field

How can I modify the below dates so that the year is dynamic but formatting stays the same?

[1/1/2018],
[2/1/2018],
[3/1/2018],
[4/1/2018],
[5/1/2018],
[6/1/2018],
[7/1/2018],
[8/1/2018],
[9/1/2018],
[10/1/2018],
[11/1/2018],
[12/1/2018]

15 Replies
Anonymous
Not applicable
Author

I don't believe so with how it's formatted. I attached an example

bjendrick
Contributor III
Contributor III

My guess is that whatever file is the source of your information will probably change with 2019.  Each column you have is formatted as text in the Excel file, so I'm not sure how this could be dynamic.  You might need to modify the import each year, or determine a way to only specify column letters with your Qlik import.

Anonymous
Not applicable
Author

Correct each year the Excel file is changed manually with the new year.

I want the script to be dynamic so I don't have to keep changing it.

I tried to set

v1stMonthCurrYear= '['&'1/1/' & Year(Today())&']'

but that didn't seem to work when I pulled in the variable.

sunny_talwar

If the number of fields don't change and there placement don't change, you can try to work with LOAD * instead of loading with the field names?

krishna_2644
Specialist III
Specialist III

Tried to make as simple as possible and as dynamic as possible.

1.PNG

try this script. when the date changes to 1/1/2019 automatically you'll be having all the dates for 2019.

check out the attached too.

let vYear = year(today());


for i = 1 to 12


data:

load '[' & Date(Date#($(i) & '/1/' & $(vYear),'mm/dd/yyyy'),'mm/dd/yyyy') & ']'as Date

AutoGenerate 1;


next i

krishna_2644
Specialist III
Specialist III

Check this qvw if you are loading from an excel file.

the year is dynamic.

1.PNG