topic How Change the beginning day of a week from Monday to some other Day? in QlikView

How Change the beginning day of a week from Monday to some other Day?

syed_muzammil — Tue, 23 Nov 2010 13:26:34 GMT

Hi All,

In Qlikview the week begins on Monday.Is there any shorter way to start the week from Saturday or any other day?

Thanks All.

How Change the beginning day of a week from Monday to some other Day?

fernandotoledo — Tue, 23 Nov 2010 18:46:01 GMT

Depends on what you are trying to do.

If you want to display the weekdays in a chart in the correct order, you can load an table mapping the weekdays to the correct order and then order them by this column value.

If you are using it in a calculation, remember that weekday is also a number and you can use someting like " WEEKDAY(DATE) -1 " to move your first day from monday to sunday

How Change the beginning day of a week from Monday to some other Day?

johnw — Tue, 23 Nov 2010 20:44:26 GMT


Fernando D. T. wrote:Depends on what you are trying to do.

Quoted for emphasis.

Yes, you can make weeks start on Saturday or any other day. How best to do this depends entirely on what your data looks like and what you're trying to do. So what does your data look like, and what are you trying to do?

How Change the beginning day of a week from Monday to some other Day?

syed_muzammil — Wed, 24 Nov 2010 09:10:56 GMT

Hi,

Thanks for your replies. I have a Pivot table with Year, Month, Week,WeekDay and Day as Dimensions. For now the Week days are shown from Monday to Sunday per week, but the Client wants its week to begin from Saturday to Friday. I have tried everything i could ?

Regards,

Syed.

How Change the beginning day of a week from Monday to some other Day?

johnw — Wed, 24 Nov 2010 18:55:54 GMT

Perhaps this?

weekstart(Date,0,-2) as Week,
dual(weekday(Date),mod(weekday(Date)+2,7)) as WeekDay,

I'm not sure if this is quite how you want your weeks defined, though. For instance, you might want a numeric week number, which is a little more work, and also requires me to know your definition of week number, since conventions vary widely. But as far as the week start, I suspect that would work.

How Change the beginning day of a week from Monday to some other Day?

syed_muzammil — Sat, 27 Nov 2010 21:43:31 GMT

Hi,

Thanks a lot John. The solution fits exactly into my requirement except for a very small change.

mod(weekday(Date)+2,7) instead of

mod(weekday(Date)+2,6) 😄

Thanks once Again.

How Change the beginning day of a week from Monday to some other Day?

johnw — Mon, 29 Nov 2010 18:56:46 GMT

Oops! You don't use 6-day weeks? 😉

OK, fixed the previous post for anyone looking for the answer for similar questions.

How Change the beginning day of a week from Monday to some other Day?

Fri, 07 Jan 2011 19:13:00 GMT

Hi all,

I'm using the weekday function on a date field in a dimension of a bar chart. Is there a way to change the first shown day (Monday)? That's because my fiscal week starts on Sunday. If you already answerd to this in this topic, sorry but I didn't understand that completely.

Many thanks!

How Change the beginning day of a week from Monday to some other Day?

johnw — Fri, 07 Jan 2011 19:39:19 GMT

I don't know if this is the best way, but it's one way. Before you load your main data, do this:

[Weekday Sort Order]:
LOAD weekday(makedate(2011,1,1)+recno()) as Weekday
AUTOGENERATE 7;

And then after you load your main data, do this:

DROP TABLE [Weekday Sort Order];

Then sort your table by original load order instead of numeric order. Probably change your default sort order for the field too. See attached.

How Change the beginning day of a week from Monday to some other Day?

Wed, 12 Jan 2011 11:49:30 GMT

Hi John, thanks for your answer but it's not working for me. This is what I did:

[Weekday Sort Order]:
LOAD weekday(makedate(2011,1,1)+recno()) as MasterCalendar_Shipments_Weekday
AUTOGENERATE 7;

MasterCalendar_Shipments:
Load *,
DayStart(MasterCalendar_Shipments_Date) as MasterCalendar_Shipments_ID_Date
where exists(MasterCalendar_Shipments_ID_Date, DayStart(MasterCalendar_Shipments_Date));
LOAD
Date(Date,'DD/MM/YYYY') as MasterCalendar_Shipments_Date,
weekday(Date(Date,'DD/MM/YYYY')) as MasterCalendar_Shipments_Weekday

FROM Master_Dates.xls
(biff, embedded labels, table is Master_Dates$);

DROP TABLE [Weekday Sort Order];

(The first load of the MasterCalendar_Shipments table is needed to link the calendar to other tables and to keep only the available dates)

Then I built the object using as dimension MasterCalendar_Shipments_Weekday and ordering by original load order but nothing changed. Can you guess where I'm wrong? Thanks again for your help.

How Change the beginning day of a week from Monday to some other Day?

johnw — Wed, 12 Jan 2011 22:35:49 GMT

That all looks right to me. I'm not sure where it would be going wrong.

How Change the beginning day of a week from Monday to some other Day?

Wed, 27 Apr 2011 17:31:52 GMT

Hi,

I changed the order of the DayNames in the document settings of my script and it worked well for me. Hopefully this helps someone. See below..

SET

DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

How Change the beginning day of a week from Monday to some other Day?

johnw — Wed, 27 Apr 2011 20:25:18 GMT


talleyrobyn wrote:Hi, I changed the order of the DayNames in the document settings of my script and it worked well for me. Hopefully this helps someone. See below.. 

SET DayNames='Sun;Mon;Tue;Wed;Thu;Fri;Sat';

VERY bad idea. Your weeks might look like they start with Sunday, but that's because you're calling Monday Sunday. See attached. April 4 is not Sunday as displayed in the first column, it's Monday. We can see this in the second column. Likewise, if we use the weekstart() function on our date, our weeks still start on Mondays - March 28, April 4, etc.