Monthend date from Month

[nareshthavidishetty]

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..

[tresesco]

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?' .

[nareshthavidishetty]

Hi,

If we have Calander month as below format

Calendar_Month = 201708,201709

Can we extract date from that.

Thanks..

[tresesco]

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

[nareshthavidishetty]

Hi,

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

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

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

Thanks..

[tresesco]

Include your desired format in date(), like:

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

[Anil_Babu_Samineni · Accepted solution]

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