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

[syed_muzammil]

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.

[fernandotoledo]

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

[johnw]

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?

[syed_muzammil]

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.

[johnw · Accepted solution]

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.

[syed_muzammil]

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.

[johnw]

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

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

[#8]

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!

[johnw]

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.

[#10]

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.