Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Contributor III
Contributor III

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]

1 Solution

Accepted Solutions
Highlighted
Specialist III
Specialist III

Re: Creating Date Field

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

View solution in original post

15 Replies
Highlighted

Re: Creating Date Field

Year is dynamic? Would you be able to elaborate?

Highlighted
Contributor III
Contributor III

Re: Creating Date Field

So that the year changes with each new year.

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

Highlighted
Contributor III
Contributor III

Re: Creating Date Field

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

Highlighted

Re: Creating Date Field

May be just use SetDateYear() function

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

Highlighted
Contributor III
Contributor III

Re: Creating Date Field

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

Highlighted
Contributor III
Contributor III

Re: Creating Date Field

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

Highlighted
Contributor III
Contributor III

Re: Creating Date Field

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?

Highlighted
Contributor III
Contributor III

Re: Creating Date Field

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

   

1/1/20182/1/20183/1/20184/1/20185/1/20186/1/20187/1/20188/1/20189/1/201810/1/201811/1/201812/1/2018
Highlighted
Contributor III
Contributor III

Re: Creating Date Field

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?