topic Re: Problem with Dayname function in QlikView

Problem with Dayname function

Thu, 14 Nov 2013 11:09:23 GMT

Hello, I'm very very new to Qlikview. I have a problem with Dayname function along with another problem with Day but i guess solving this one will solve the other too.

I'm trying to make a dashboard from the data I take from a SQL database.

Time is held on a DOCUMENT_Date field on the database in "2013-11-12 00:00:00.000" format.

When I use DayName ("DOCUMENT_DATE") the output list is in DD.MM.YYYY (i.e 01.11.2013) format instead of day names.

I tried DayName ("DOCUMENT_DATE", 'YYYY-MM-DD') and DayName ("DOCUMENT_DATE", 'YYYY-MM-DD hh:mm:ss[.fff') but both gave me blank lists.

Also the problem about the Day function is that it's giving me months list instead of a day list but I guess solving the above will solve it too. I hope I'm not being very optimistic

What am I doing wrong?

Re: Problem with Dayname function

tresB — Thu, 14 Nov 2013 11:17:35 GMT

What is the output you are expecting? DayName() function does not take a format parameter. If you are trying to get day names like : 'Sun', 'Mon' then you can try with WeekDay() function.

Re: Problem with Dayname function

Anonymous — Thu, 14 Nov 2013 11:22:27 GMT

Ege

Try the Weekday() function instead.

Best Regards, Bill

Re: Problem with Dayname function

Thu, 14 Nov 2013 11:29:03 GMT

Hi Ege

Please refer Help, you will get more details

dayname ( '2006-01-25 16:45' ) returns '2006-01-25' with an underlying numeric value corresponding to '2006-01-25 00:00:00.000'

dayname ( '2006-01-25 16:45', -1 ) returns '2006-01-24' with an underlying numeric value corresponding to '2006-01-24 00:00:00.000'

dayname ('2006-01-25 16:45', 0, 0.5 ) returns '2006-01-25' with an underlying numeric value corresponding to '2006-01-25 12:00:00.000

=weekday( '2013-11-14' ) O/P THU

Re: Problem with Dayname function

Thu, 14 Nov 2013 12:14:32 GMT

Thank you and other responders for hep, I managed to get the day names as I wanted but I still can't extract the Day from the Document_Date. I'm trying to create a day list in day numbers.

I tried 3 different approaches as ;

Date# (Day("DOCUMENT_DATE"),'YYYY-MM-DD hh:mm:ss[.fff]')

Date# (Day("DOCUMENT_DATE"), 'DD-MM-YYYY') ,

Day ("DOCUMENT_DATE")

but all three are giving me a month list instead of a day list. Can you help me with this one too?

DOCUMENT_Date field in database is in YYYY-MM-DD format

Re: Problem with Dayname function

tresB — Thu, 14 Nov 2013 12:17:28 GMT

Try like:

Day(Date# ("DOCUMENT_DATE"),'YYYY-MM-DD hh:mm:ss[.fff]') )

Re: Problem with Dayname function

Thu, 14 Nov 2013 12:40:49 GMT

Didn't work, gave a blank list. Any other ideas?

Re: Problem with Dayname function

tresB — Thu, 14 Nov 2013 12:44:59 GMT

Sorry, parenthesis error!

Try:

Day(Date# ("DOCUMENT_DATE",'YYYY-MM-DD hh:mm:ss[.fff]') )

Re: Problem with Dayname function

Thu, 14 Nov 2013 12:48:10 GMT

I noticed that at your first respond and typed with correct paranthesis, that was the time it didn't work

Re: Problem with Dayname function

tresB — Thu, 14 Nov 2013 12:54:42 GMT

One more correction , remove '[]' around 'fff' like:

Day(Date# ("DOCUMENT_DATE",'YYYY-MM-DD hh:mm:ss.fff') )

Re: Problem with Dayname function

Thu, 14 Nov 2013 13:00:31 GMT

Once again a blank list

I don't get it..why is not working?

DOCUMENT_DATE is like "2013-11-12 00:00:00.000" in Database. Isn't it YYYY-MM-DD hh:mm.ss.fff format? Am I seeing things wrong?

This data is only for November so I'm sure we are not mixing months and days.

Re: Problem with Dayname function

tresB — Thu, 14 Nov 2013 13:12:14 GMT

Could you please upload a sample app with small data set directly from DB?

Re: Problem with Dayname function

Fri, 15 Nov 2013 09:52:16 GMT

Sorry I can't upload any data from the database due to agreements between client and firm

I managed to get the day list from another table but now they are shifted by 1. For example it's showing that 1st of November was a saturday where it was a Friday. How can I fix this shift?