topic Re: Function to pull the day name from a date value? in QlikView

Function to pull the day name from a date value?

stevelord — Mon, 21 Dec 2015 17:45:04 GMT

Hi All, I need to pull the day name out of a date - so values like Sun Mon Tue Wed Thu Fri Sat coming out of values like 12/21/2015. What's the function? (I'll be doing trial and error while this post waits for a correct answer. Someone wants to know most popular day of the week for users to log their entries.)

Re: Function to pull the day name from a date value?

sunny_talwar — Mon, 21 Dec 2015 17:46:31 GMT

Try WeekDay() function

Re: Function to pull the day name from a date value?

swuehl — Mon, 21 Dec 2015 17:49:51 GMT

(I'll be doing trial and error while this post waits for a correct answer.

You shouldn't do this by trial and error. There is a reference manual and a HELP function available, besides lots of books, online videos, blogs etc.

The chapter in the HELP is called: 'Date and time functions'

Re: Function to pull the day name from a date value?

stevelord — Mon, 21 Dec 2015 18:17:52 GMT

Thanks, maybe I meant to say 'consulting the reference manual in case there's no quick answer'. Reference manual might've had a straight answer this time, but plenty of times it has answered the questions to the left and right of mine and I'm doing 'trial and error' from the reference manual itself. So I developed a habit to get the ball rolling on qlikcommunity in case my own effort comes up empty. And I share my answer if I get it before anyone else does (citing wherever I got it from).

Re: Function to pull the day name from a date value?

JonnyPoole — Mon, 21 Dec 2015 19:08:24 GMT

Try WWW or WWWW as the date mask. The inner date# may not be required for you if you are already reading as a date value.

Date( Date#( '12/21/2015','MM/DD/YYYY'), 'WWWW')

Re: Function to pull the day name from a date value?

JonnyPoole — Mon, 21 Dec 2015 19:09:12 GMT

even better !

=if( Date( Date#( '12/21/2015','MM/DD/YYYY'), 'WWW') = 'Mon', 'Hooray its a monday', 'try again')

Re: Function to pull the day name from a date value?

rwunderlich — Mon, 21 Dec 2015 19:41:36 GMT

Jonathan,

date(mydate,'WWWW') does not extract the weekday from a date. It only formats the display. If the intention is to bucket by weekday, 'WWWW' Monday on two different dates would return two buckets, not a single "Monday" bucket.

-Rob

Re: Function to pull the day name from a date value?

Mon, 21 Dec 2015 19:44:06 GMT

Hi Steve try this:

=weekday(date#('12/21/2015', 'MM/DD/YYYY')) in a Text Box! The result will be: Mon (Monday)

I hope that it help you !

First you put a format for the string and then applies the weekday() function.

Regards, Agustin

Re: Function to pull the day name from a date value?

JonnyPoole — Mon, 21 Dec 2015 20:00:28 GMT

True - it won't be effective for list boxes or chart dims , just conditional checks etc..

For a chart dim or list box , here is a hack:

=Date(MakeDate(2015,12,20)+ WeekDay( Date),'WWW')

- weekday() returns 0 for Sunday and 6 for Saturday

- 2015/12/20 , or yesterday, was a sunday. That will never change (i hope!)

- so 2015/12/20 + (0) -> Sunday 2015/12/20 displayed as 'Sun' ... it will resolve to 'sun' for every data record with a day of the week that is a sunday

- so 2015/12/20 + (1) -> Monday 2015/12/21 displayed as 'Mon' ... it will resolve to 'mon' for every data record with a day of the week that is a monday

etc..

Re: Function to pull the day name from a date value?

swuehl — Mon, 21 Dec 2015 20:06:53 GMT

Ok ... but what's the advantage over just using

Weekday(Date)

then?

Re: Function to pull the day name from a date value?

JonnyPoole — Mon, 21 Dec 2015 20:13:46 GMT

oh jeez! none.

i was expecting the function to just return an integer, but it returns a dual with the day names in WWW format no less.

You can definitely call me out for not testing before posting... but perhaps based on our qlik documentation ("returns an integer") we should make some updates.

Perhaps this was the same reason for the original post from the member?

Here is the documentation for latest and greatest SR12.

----------------

weekday(date)

Week day. Returns an integer between 0-6.

Example:

weekday( '1971-10-30' ) returns 5.

If the date format used does not correspond to the one set in your operating system, QlikView will not be able to make a correct interpretation. See above under day(date).

Re: Function to pull the day name from a date value?

rwunderlich — Mon, 21 Dec 2015 22:13:37 GMT

Jonathan, I see where you were coming from. The doc is wrong.

The QV12 doc is correct.

Note that in QV12, an optional weekstart parameter is now available.

-Rob

Re: Function to pull the day name from a date value?

MarcoWedel — Tue, 22 Dec 2015 00:13:38 GMT

Hi,

to get long week day names instead of the short names the WeekDay() function returns, you could also use something like:

SET vLongWeekDay = Dual(Date($1,'WWWW'),WeekDay($1));

table1:
LOAD *,
    $(vLongWeekDay(DateField)) as LongWeekDay,
    WeekDay(DateField) as WeekDay
Inline [
    DateField
    12/21/2015
    12/22/2015
    12/23/2015
    12/24/2015
    12/25/2015
    12/26/2015
    12/27/2015
    12/28/2015
    12/29/2015
    12/30/2015
    12/31/2015
];

hope this helps

regards

Marco