topic Re: create last day of a month in QlikView

create last day of a month

Fri, 18 Mar 2016 08:36:28 GMT

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

Re: create last day of a month

sunny_talwar — Fri, 18 Mar 2016 08:38:25 GMT

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

Re: create last day of a month

Chanty4u — Fri, 18 Mar 2016 08:40:27 GMT

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

Re: create last day of a month

jonathandienst — Fri, 18 Mar 2016 08:41:32 GMT

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)

Re: create last day of a month

Kushal_Chawda — Fri, 18 Mar 2016 08:42:30 GMT

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

Re: create last day of a month

Anonymous — Fri, 18 Mar 2016 08:42:30 GMT

Truely Date means?

No Time Involvement, is it?

Re: create last day of a month

sunny_talwar — Fri, 18 Mar 2016 08:44:28 GMT

Yes, MonthEnd includes time as well

Re: create last day of a month

Anonymous — Fri, 18 Mar 2016 08:47:00 GMT

Okay, got it

Re: create last day of a month

Wed, 23 Mar 2016 08:01:05 GMT

thank you so much for all your help.

Chris

Re: create last day of a month

Wed, 18 May 2016 15:12:33 GMT

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!