Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
BARC’s The BI Survey 19 makes it official. BI users love Qlik. GET REPORT
wajihakhan
New Contributor III

Date field from Period field

Hi,

Is it possible to make a date field from period field?

Like 01.01.2018 from 01.2018

       DD.MM.YYYY     MM.YYYY

Thanks

1 Solution

Accepted Solutions
Partner
Partner

Re: Date field from Period field

Load date(MonthStart(Date#(dateField,'YYYYMM')),'DD.MM.YYYY') as NewDateField Inline [

dateField

201801

201802

201803

201804

];

result:

Capture.PNG

View solution in original post

18 Replies
Luminary
Luminary

Re: Date field from Period field

Yes, you can use Date#() function for this. It takes two parameters: a string with date value and a string defining date format. So in your exmple:

Date#('01.2018', 'MM.YYYY')

Of course you can use field names as the first parameter:

Date#(DateField, 'MM.YYYY')

Hope this helps

Juraj

Highlighted
Chanty4u
Esteemed Contributor III

Re: Date field from Period field

try this

A:

load * Inline

[

OrdDate

01.2018

];

LOAD * ,

Date(Date#(OrdDate,'MM.YYYY'),'DD.MM.YYYY') as New

Resident A;

wajihakhan
New Contributor III

Re: Date field from Period field

I don't have date field. I have only Period field from which i want to make a date field.

Date# just generate the same period

wajihakhan
New Contributor III

Re: Date field from Period field

By doing so, i got blank output.

Luminary
Luminary

Re: Date field from Period field

Date#() generates the same period, but makes Qlik understand it is a date field. So after using Date#() on your period field you can work with it as with date field. Try Chanty's example to see for yourself

Date(Date#(PeriodField, 'MM.YYYY'), 'DD.MM.YYYY')

Partner
Partner

Re: Date field from Period field

can u share 5 dates you already have? exactlly how they're written; this could help us

Partner
Partner

Re: Date field from Period field

Hi,

You need to complete the date then change it,

DATE(DATE#('01.'&DateField,'DD.MM.YYYY'),'DD.MM.YYYY') AS NewDate


This will make all your period the 1st of the month.


Mark

Re: Date field from Period field

Then please share some real input data as this works for me.

wajihakhan
New Contributor III

Re: Date field from Period field

I have period field in below format

a.PNG