Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

karlykrebs
New 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
krishna_2644
Valued Contributor 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

Re: Creating Date Field

Year is dynamic? Would you be able to elaborate?

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

karlykrebs
New 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

Re: Creating Date Field

May be just use SetDateYear() function

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

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

karlykrebs
New Contributor III

Re: Creating Date Field

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

bjendrick
New 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?

karlykrebs
New 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
bjendrick
New 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?