Your data looks like this and you need to report how many days an employee has worked.

So if ‘person 1’ starts at the first of January you would expect expect according to the calendar (2015) that this person worked 2 days in the first week of January.

*Note: This example only works with a fixed date range for the columns which will be unpivoted, these columns will be created at step 3.*

- Create some flexible properties.

- Add the start week property to your data table and save it as ‘
**start date**‘.

1Date(${PropertyStartWeek}) - Add for every week in the year a column and increase the weeks like w0, w1, w2, w.. This formula generates a start week for every week in the year by increasing the start week.

For column w0 the formula will be:1DateAdd("week",0,[start date]) - Add for every week in the year a column with the corresponding week number 0, 1, 2, .. This formula will calculate the number of days worked per week.

For column 0 the formula will be:1234Integer(casewhen ([end contract]<[w0]) or ([start contract]>DateAdd("day",${PropertyWorkdays},[w0])) then 0else DateDiff("day",If([start contract]<[w0],[w0],[start contract]),if([end contract]>=DateAdd("day",${PropertyWorkdays},[w0]),DateAdd("day",${PropertyWorkdays},[w0]),DateAdd("day",1,[end contract])))end)

The data table now looks like this:

- Unpivot the data table to a new data table and add a column to calculate the start week based on the category. See column date in the screenshot below.

1DateAdd("week",Integer([Category]),[start date])