Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
jim_chan
Specialist
Specialist

How to create Start Date and End Date

Hi Guys,

I have a Month field - Jan until Dec. Now i need to create a Start Date and End Date field, but is empty at the moment.

but i want to achieve here is for example: if is Month Jan - Start Date is 1/1/2016 and End Date is 31/01/2016.

How can i achieve that?

Rgds,

Jim

1 Solution

Accepted Solutions
MK_QSL
MVP
MVP

1) If you have Month not in Dual form, first you have to interpret it to DUAL format using Date# function.

2) you have mentioned that you have Month field only. I assumed that you don't have date and year fields.

    If you have Year field, you can use as below.

=MonthStart(MakeDate(Year(Date#(Year,'YYYY')),Month(Date#(Month,'MMM'))))

 

=MonthEnd(MakeDate(Year(Date#(Year,'YYYY')),Month(Date#(Month,'MMM'))))

3) If you want to use

     MonthEnd(StartDate) as EndDate, you need to use Preceding Load.

Provide your exact requirements with some sample data.

View solution in original post

10 Replies
MK_QSL
MVP
MVP

=MonthStart(MakeDate(Year(Today()),Month(Date#('Jan','MMM'))))

=MonthEnd(MakeDate(Year(Today()),Month(Date#('Dec','MMM'))))

anagharao
Creator II
Creator II

Hi ,

You could use MonthStart and MonthEnd functions

Example:

=MonthStart(DATE#(Month(TODAY())&Year(TODAY()),'MMMYYYY'))

In the above example you would have to use Month and Year field instead of 'Month(TODAY())' and 'Year(TODAY())'.

jim_chan
Specialist
Specialist
Author

Hi there,

i came across this.. why not use "MakeDate()" ?

Rgds

Jim

jim_chan
Specialist
Specialist
Author

Hi there,

i came across this.. why not use "MakeDate()" ?

Rgds

Jim

jim_chan
Specialist
Specialist
Author

HI there,

Why not just create as such? :

makedate(Year, Month, 1) as StartDate,

MonthEnd(StartDate) as EndDate

MK_QSL
MVP
MVP

1) If you have Month not in Dual form, first you have to interpret it to DUAL format using Date# function.

2) you have mentioned that you have Month field only. I assumed that you don't have date and year fields.

    If you have Year field, you can use as below.

=MonthStart(MakeDate(Year(Date#(Year,'YYYY')),Month(Date#(Month,'MMM'))))

 

=MonthEnd(MakeDate(Year(Date#(Year,'YYYY')),Month(Date#(Month,'MMM'))))

3) If you want to use

     MonthEnd(StartDate) as EndDate, you need to use Preceding Load.

Provide your exact requirements with some sample data.

anagharao
Creator II
Creator II

yup,

You could do that as well.

tresesco
MVP
MVP

You CAN use makedate() here, provided your Month field is numeric. If that is not the case, you could still use it like:

MakeDate(Year, Month(Date#(Month, 'MMM')), 1)

Here, Month(Date#(Month, 'MMM')) - converts your text months into numbers.

jim_chan
Specialist
Specialist
Author

thank you master. thank you!