How to get worked hours from custom week(friday to friday)

[#1]

Hi,

I want to show a (bar)chart where the total working hours for every employee is visible. This must be for every week. However, the week period is from friday to friday. For example: the friday from week 1 to the friday from week 2. How can I achieve this?

I want something like this, but the period for a week is thus from friday to friday.

"Monteur" is an Employee.

Can someone show me (with some sample code) how to calculate this type of weeks?

[Clever_Anjos]

Try a calendar like this

LOAD
  date(today()-RowNo()) as Data,
  WeekStart(today()-RowNo(),0,4) as Week
autogenerate 50;

[jvitantonio]

Hi Nazeem,

You need to create in your model a Week field that starts on Friday. Let's supposed you have a Date field in your fact table. (Or in your Calendar table). you will then do:

Load
Date,
weekstart(Date,0,-3) as WeekStartFriday
FROM...

Weekstart will create a field date with the day starting each Friday. You can then use in your chart (or create a new field) like week(WeekStartFriday) to have the week number that you can use in your chart.

Find an example attached.

JV

BI Experience | A place to share our Business Intelligence experiences