topic Re: How to create Start Date and End Date in QlikView

How to create Start Date and End Date

jim_chan — Fri, 22 Jul 2016 07:54:32 GMT

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

Re: How to create Start Date and End Date

MK_QSL — Fri, 22 Jul 2016 08:02:03 GMT

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

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

Re: How to create Start Date and End Date

anagharao — Fri, 22 Jul 2016 08:02:37 GMT

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())'.

Re: How to create Start Date and End Date

jim_chan — Fri, 22 Jul 2016 08:04:44 GMT

Hi there,

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

Rgds

Jim

Re: How to create Start Date and End Date

jim_chan — Fri, 22 Jul 2016 08:04:55 GMT

Hi there,

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

Rgds

Jim

Re: How to create Start Date and End Date

jim_chan — Fri, 22 Jul 2016 08:08:38 GMT

HI there,

Why not just create as such? :

makedate(Year, Month, 1) as StartDate,

MonthEnd(StartDate) as EndDate

Re: How to create Start Date and End Date

MK_QSL — Fri, 22 Jul 2016 08:13:25 GMT

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.

Re: How to create Start Date and End Date

anagharao — Fri, 22 Jul 2016 08:18:59 GMT

yup,

You could do that as well.

Re: How to create Start Date and End Date

tresB — Fri, 22 Jul 2016 08:29:12 GMT

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.

Re: How to create Start Date and End Date

jim_chan — Fri, 22 Jul 2016 08:29:26 GMT

thank you master. thank you!

Re: How to create Start Date and End Date

jim_chan — Fri, 22 Jul 2016 08:29:44 GMT

thank you bro!