Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: create last day of a month

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

9 Replies

Re: create last day of a month

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

sureshqv
Esteemed Contributor III

Re: create last day of a month

   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

MVP
MVP

Re: create last day of a month

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

Re: create last day of a month

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

balrajahlawat
Esteemed Contributor

Re: create last day of a month

Truely Date means?

No Time Involvement, is it?

Re: create last day of a month

Yes, MonthEnd includes time as well

Capture.PNG

balrajahlawat
Esteemed Contributor

Re: create last day of a month

Okay, got it

Not applicable

Re: create last day of a month

thank you so much for all your help.

Chris

Not applicable

Re: create last day of a month

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!

Community Browser