Solved: Re: Creating Date Field - Qlik Community

Anonymous · ‎2018-02-28

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]

Qrishna · ‎2018-03-01

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

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

View solution in original post

sunny_talwar · ‎2018-02-28

Year is dynamic? Would you be able to elaborate?

Anonymous · ‎2018-02-28

So that the year changes with each new year.

For example, next year it will change to 2019 automatically.

Anonymous · ‎2018-02-28

I know that part would be Year(Today()). I just don't know how to combine it with the other set part

sunny_talwar · ‎2018-02-28

May be just use SetDateYear() function

Date(SetDateYear(DateField, Year(Today()))

bjendrick · ‎2018-02-28

I've used the MakeDate() function and the Year() function to create values like that, by being able to specify the pieces of the final date you desire. For your example, you can do the following:

MakeDate(Year(Now()), 1, 1) // 01/01/2018

MakeDate(Year(Now()), 2, 1) // 02/01/2018

MakeDate(Year(Now()), 3, 1) // 03/01/2018

.

Because you have the Year() function as well, it will calculate based on current year.

https://help.qlik.com/en-US/sense/November2017/Subsystems/Hub/Content/Scripting/DateAndTimeFunctions...

Anonymous · ‎2018-03-01

This works, but not when I try it in a cross table. How can I change that?

bjendrick · ‎2018-03-01

I'm unsure... you might try assigning each to a variable that you can call.

Are you pulling these values from some base data to generate them dynamically? Is there a table or another source that you're relying on for your cross table?

Anonymous · ‎2018-03-01

I'm pulling from an excel file with the dates as columns:

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

bjendrick · ‎2018-03-01

Is it possible that you don't need to modify the dates since they are provided by the Excel file? Meaning, that file is already dynamic, and you won't need to modify your results because they come into Qlik as you need them?