topic Re: Monthend date from Month in QlikView

Monthend date from Month

nareshthavidishetty — Wed, 25 Nov 2020 16:16:04 GMT

Hi,

We have aggregated sales data in database itself due to performence and pulled that data in qlik.

We have only Calender_Month date field was available.

When i try to ectract monthend dates from month using below

MonthEnd(Calender_Month )

It giving wrong dates.

Is there any other way to get dates from Calender_Month .

Thanks..

Re: Monthend date from Month

Anil_Babu_Samineni — Tue, 14 Nov 2017 09:19:41 GMT

What you mean wrong dates? And, can you explain the data you have and you can use MonthEnd(DateField) ..

Re: Monthend date from Month

nareshthavidishetty — Tue, 14 Nov 2017 09:23:36 GMT

Hi ,

We have

Calender_Month as 1,2,3,4,5,6,..

When I'm tring to do the below

Monthend(Calender_Month)

It gives wrong dates.

Thanks...

Re: Monthend date from Month

YoussefBelloum — Tue, 14 Nov 2017 09:25:45 GMT

the MonthEnd fuction takes a Date not a month

Re: Monthend date from Month

pradosh_thakur — Tue, 14 Nov 2017 09:29:09 GMT

=monthend(MakeDate(year(today()),Calender_Month,1))

Re: Monthend date from Month

Anil_Babu_Samineni — Tue, 14 Nov 2017 09:30:00 GMT

You can read this makedate ‒ QlikView

Re: Monthend date from Month

tresB — Tue, 14 Nov 2017 09:49:25 GMT

If you wish to get the last day of the month from a numeric month field, you can try like:

Day(MonthEnd(Date#(Calendar_Month,'M')))

This will give 31 for the months 1,3,5.. and 30 or 28 accordingly for such months. However, if you want a date, there has to be a year element also in the source. If you have it in the field you could use makedate() or date#() for the same like:

MonthEnd(MakeDate(Year, Calendar_Month))

Re: Monthend date from Month

nareshthavidishetty — Tue, 14 Nov 2017 11:34:18 GMT

Hi,

We have only these fields in table

SELECT Calendar_Month,

Month_Material_Count,

ConformedType

FROM XXXX.XXX

Is it possible to extract date from Calendar_Month

Calendar_Month detail 1,2,3,4..

Thanks..

Re: Monthend date from Month

pradosh_thakur — Tue, 14 Nov 2017 11:38:27 GMT

did you try this

=monthend(MakeDate(year(today()),Calender_Month,1))

Re: Monthend date from Month

nareshthavidishetty — Tue, 14 Nov 2017 11:45:25 GMT

Yeah I have tried.

It gives only for year..but I do have data from past 3 years.

May be because of year(today()).

Thanks..

Re: Monthend date from Month

tresB — Tue, 14 Nov 2017 11:46:36 GMT

It would be better if you ask the question to yourself. It's not about technicality; it's about logic. 1 means the month Jan, but of which year? Without a year element it's not a date. Generally for monthly data day is considered as 1, or any date of your choice for that matter. For year also there has to be value. If you have one year data and and you know the year, you can always use (hard code) that while making the date in the expression. If the year is not important, you can use a dummy year or current year and opt to not show that in the UI. It's all upon what and how you want. Ask yourself/business - 'what is expected?' .

Re: Monthend date from Month

nareshthavidishetty — Tue, 14 Nov 2017 12:04:33 GMT

Hi,

If we have Calander month as below format

Calendar_Month = 201708,201709

Can we extract date from that.

Thanks..

Re: Monthend date from Month

tresB — Tue, 14 Nov 2017 12:11:29 GMT

Date(Date#('201708', 'YYYYDD')) will give you 1-Aug-2017. If this is fine with you, you can try like:

Date(Date#(Calendar_Month, 'YYYYDD')) as Date

Re: Monthend date from Month

nareshthavidishetty — Tue, 14 Nov 2017 12:42:56 GMT

Hi,

We tried the below and we got '08-01-2017'

But we need the format 'DD-MMM-YYYY'.

Date(Date#('201708', 'YYYYDD'))

Thanks..

Re: Monthend date from Month

tresB — Tue, 14 Nov 2017 12:48:27 GMT

Include your desired format in date(), like:

Date(Date#('201708', 'YYYYDD'),'DD-MMM-YYYY')

Re: Monthend date from Month

Anil_Babu_Samineni — Tue, 14 Nov 2017 12:48:59 GMT

Time god, Finally you will be conclusion stage, Try this?

Date(Date#('201708', 'YYYYDD'),'DD-MMM-YYYY')

Or Even this can be work

Date(MonthEnd(MakeDate(Left(FieldName, 4), Right(FieldName,2))), 'DD-MMM-YYYY')