topic Re: Last thursday date for CM from today() function in QlikView

Last thursday date for CM from today() function

Anonymous — Tue, 06 Dec 2016 18:46:31 GMT

Hi experts.

I'm looking for some way to get calendar options from today() function,

Can QV get date for last thursday for current month using today()?

For example, in dimension i need to get 29.12.2016

Thanks.

Re: Last thursday date for CM from today() function

Anonymous — Tue, 06 Dec 2016 18:56:22 GMT

https://community.qlik.com/message/1103506#1103506

Re: Last thursday date for CM from today() function

Anonymous — Tue, 06 Dec 2016 19:18:23 GMT

Thanks.

Could you explain me please, how it works?

Re: Last thursday date for CM from today() function

vishsaggi — Tue, 06 Dec 2016 20:15:21 GMT

From Reference Manual Help:

lastworkdate(start_date, no_of_workdays {, holiday})

Returns the earliest ending date to achieve number_of_workdays (Monday-Friday) if starting at start_date taking into account any optionally listed holidays. Start_date and holiday should be valid dates or timestamps.

Examples:

lastworkdate ('2007-02-19', 9) returns '2007-03-01'

lastworkdate ('2006-12-18', 8, '2006-12-25', '2006-12-26') returns '2006-12-29'

Re: Last thursday date for CM from today() function

vishsaggi — Tue, 06 Dec 2016 20:26:40 GMT

Try this?

= FirstWorkDate(Monthend(Today()), 2)

Re: Last thursday date for CM from today() function

swuehl — Tue, 06 Dec 2016 22:44:17 GMT

If you are specifically searching for the last thursday of a month, then maybe use something like

=DayName(Monthend(today())- Mod(Weekday(Monthend(today()))-3,7))

Re: Last thursday date for CM from today() function

johnw — Tue, 06 Dec 2016 23:56:05 GMT

Only works for some months. For days in November, for instance, it will return 11/29/16, which is a Tuesday.

Re: Last thursday date for CM from today() function

vishsaggi — Wed, 07 Dec 2016 14:39:54 GMT

Oops I missed that. Thanks John for that.

Re: Last thursday date for CM from today() function

swuehl — Wed, 07 Dec 2016 16:42:16 GMT

Or if you want to flag dates in your master calendar, you can do it like

LOAD *,

if(Weekday(Date) =3 and Day(Date) >Day(Monthend(Date))-7,1,0) as Flag1,

If(Date = Floor(Monthend(Date)- Mod(Weekday(Monthend(Date))-3,7)),1,0) as Flag2

;

LOAD *, Weekday(Date) as Weekday;

LOAD Date(Makedate(2016,1,1)+recno()-1) as Date

AutoGenerate 365;

Date	Weekday	Flag1	Flag2
28.01.2016	Do	1	1
25.02.2016	Do	1	1
31.03.2016	Do	1	1
28.04.2016	Do	1	1
26.05.2016	Do	1	1
30.06.2016	Do	1	1
28.07.2016	Do	1	1
25.08.2016	Do	1	1
29.09.2016	Do	1	1
27.10.2016	Do	1	1
24.11.2016	Do	1	1
29.12.2016	Do	1	1

Re: Last thursday date for CM from today() function

Anonymous — Wed, 07 Dec 2016 16:50:26 GMT

Dears, thanks for help!

Re: Last thursday date for CM from today() function

Anonymous — Wed, 07 Dec 2016 16:51:10 GMT

Hi Stefan!

Many thanks, it works using different dates for any months:)

Re: Last thursday date for CM from today() function

tamilarasu — Wed, 07 Dec 2016 18:19:45 GMT

Hello Андрей,

Though you got the solution, I would like to share the below one.

=Date(MonthEnd(Today()) - Pick(WeekDay(MonthEnd(Today())),4,5,6,0,1,2,3))

Re: Last thursday date for CM from today() function

johnw — Wed, 07 Dec 2016 18:23:04 GMT

I like it. Mod() works, and I usually prefer mathematical solutions, but the pick() reads more clearly to me in this case.

Re: Last thursday date for CM from today() function

tamilarasu — Thu, 08 Dec 2016 02:04:26 GMT

Hi John,

I am glad that you liked it. I was not able to understand Mod() solution at the first glance. So I thought of sharing this, because this one seems to be easily understandable.