Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

create last day of a month

Hi,

I hope someone of you could help me.

I Need to create a datefield but I dont have everything needed.

I have a month-field (txnper = '2') and I have a year-field (txnyear = '2016'). This are not datefields.

How can i create from this two fields a new one called txndate with entry of the last day from February 2016 (29.02.2016)

I got through many Posts here, but I could find anyone, which has not a dateformat as base.

Thanks in advance.

Chris

1 Solution

Accepted Solutions
sunny_talwar

Try this:

Date(MonthEnd(MakeDate(txnyear, txnper)), 'DD.MM.YYYY') as Date


UPDATE: I would also add Floor in there to make sure that this is truly date

Date(Floor(MonthEnd(MakeDate(txnyear, txnper))), 'DD.MM.YYYY') as Date

View solution in original post

9 Replies
sunny_talwar

Try this:

Date(MonthEnd(MakeDate(txnyear, txnper)), 'DD.MM.YYYY') as Date


UPDATE: I would also add Floor in there to make sure that this is truly date

Date(Floor(MonthEnd(MakeDate(txnyear, txnper))), 'DD.MM.YYYY') as Date

View solution in original post

Chanty4u
Champion III
Champion III

   IF(Floor( month-field ) = Floor(MonthEnd( year-field )),Value,0) as LastDay


chk below

last day from dimension month

Help - last day of Month

Last day of month Value

jonathandienst

When loading the txnper/txnyear fields, add this line to the load:

LOAD

     ....

     Date(Floor(MonthEnd(MakeDate(txnyear, txnper)))) as txndate,

     ....

MakeDate - create a date of the 1st of the relevant period

MonthEnd - get the last day (including time to the last second) of the month

Floor - drop the time portion

Date - format the date using the default date format set in your environment (DateFormat variable)

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Kush
MVP
MVP

=date(monthend(date#(txnper &'-'&txnyear,'MM-YYYY')),'DD/MM/YYYY')

balrajahlawat
Champion
Champion

Truely Date means?

No Time Involvement, is it?

sunny_talwar

Yes, MonthEnd includes time as well

Capture.PNG

balrajahlawat
Champion
Champion

Okay, got it

Not applicable
Author

thank you so much for all your help.

Chris

Not applicable
Author

Hi,

Here you are your solution:

MonthEnd(AddMonths(MakeDate(Year(Today()),Month(Today())),0))

It return the last day of current month

I hope it help you!