Qlik Community

Qlik DataMarket Discussions

Discussion Board for collaboration regarding Qlik DataMarket.

divyanshi
New Contributor II

Want to get last date of the respective month from month name in Qliksense

Hi,

I want to get the last date of the respective month from the selected month name selected by a filter.

Eg:

If a month is "Jun" , then I want it should appear as "30-Jun-2017".

Kindly guide.

8 Replies
shraddha_g
Honored Contributor III

Re: Want to get last date of the respective month from month name in Qliksense

If you have a DateField in Data Model then you can try

date(Monthend(max(DateField)),'DD-MMM-YYYY')

divyanshi
New Contributor II

Re: Want to get last date of the respective month from month name in Qliksense

Thanks Shraddha.

But if I have month field then what can I do ?

shraddha_g
Honored Contributor III

Re: Want to get last date of the respective month from month name in Qliksense

Do you have Year Field?

divyanshi
New Contributor II

Re: Want to get last date of the respective month from month name in Qliksense

Yes we have year field too along with the month and date.

shraddha_g
Honored Contributor III

Re: Want to get last date of the respective month from month name in Qliksense

in Script,

Create a Monthend Field.

Date(MonthEnd(Date#(Month &' '& Year,'MMM YYYY')),'DD-MMM-YYYY') as MonthEnd

In FrontEnd use

Max(MonthEnd)

divyanshi
New Contributor II

Re: Want to get last date of the respective month from month name in Qliksense

Thanks Shraddha.

But the month end does not give any data.

And the main query is I have date field and I want to calculate Last Quarter Exit.

i.e.

If I select Aug then date should appear as 30-June-2017.

And from date field I am able to calculate Last Quarter month by using :

concat(distinct month(QuarterEnd(Date(Date#((If(num(month(date#(Mon,'MMM')))=1,num(month(date#(Mon,'MMM')))+11,num(month(date#(Mon,'MMM')))-3)),'MM')))),',')


But not getting last date of this month.

shraddha_g
Honored Contributor III

Re: Want to get last date of the respective month from month name in Qliksense

Can you share sample app?

shraddha_g
Honored Contributor III

Re: Want to get last date of the respective month from month name in Qliksense

Refer sample apps posted in Can we put condition in max function

I guess you will get desired result from that link

Community Browser