topic Re: Creating Date Field in QlikView

Creating Date Field

Anonymous — Wed, 28 Feb 2018 17:55:26 GMT

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]

Re: Creating Date Field

sunny_talwar — Wed, 28 Feb 2018 17:56:36 GMT

Year is dynamic? Would you be able to elaborate?

Re: Creating Date Field

Anonymous — Wed, 28 Feb 2018 17:58:10 GMT

So that the year changes with each new year.

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

Re: Creating Date Field

Anonymous — Wed, 28 Feb 2018 18:03:00 GMT

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

sunny_talwar — Wed, 28 Feb 2018 18:05:10 GMT

May be just use SetDateYear() function

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

Re: Creating Date Field

bjendrick — Wed, 28 Feb 2018 18:05:19 GMT

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/makedate.htm

Re: Creating Date Field

Anonymous — Thu, 01 Mar 2018 20:32:45 GMT

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

Re: Creating Date Field

bjendrick — Thu, 01 Mar 2018 20:36:52 GMT

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?

Re: Creating Date Field

Anonymous — Thu, 01 Mar 2018 20:39:50 GMT

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

Re: Creating Date Field

bjendrick — Thu, 01 Mar 2018 21:08:51 GMT

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?

Re: Creating Date Field

Anonymous — Thu, 01 Mar 2018 21:15:31 GMT

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

Re: Creating Date Field

bjendrick — Thu, 01 Mar 2018 21:21:22 GMT

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.

Re: Creating Date Field

Anonymous — Thu, 01 Mar 2018 21:27:36 GMT

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.

Re: Creating Date Field

sunny_talwar — Thu, 01 Mar 2018 22:34:43 GMT

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?

Re: Creating Date Field

Qrishna — Fri, 02 Mar 2018 04:59:25 GMT

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

Re: Creating Date Field

Qrishna — Fri, 02 Mar 2018 06:18:03 GMT

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

the year is dynamic.