Counting with dates

When you have to build an HR module you often need to work dates e.g. start date en termination date. You could also use the same function to calculate the depreciation periods for a fixed asset.

One function I use very much is a procedure which will flag the periods during a start en termination date, this is useful if you need to lock periods or if you want to filter employees in data views.

In the sample below you can enter a start and end date and if you run the procedure then Board will flag the periods between the start and end date for each employee.

I use the following procedure step for this.

a is a cube in which I store the month keys per month.

b: is the employee start date

c: is the termination date

d: is the last date in the database, so in case there is no termination date then I use this date.

e: this is a formula, which will use the termination date if it is not empty otherwise it will use the last date of the database.

f: this is a formula, which will convert the start date to a value

g: this is a formula, which will convert the end date to a value.

h: this is a formula, which will convert the monthkey (a) to a value.

i: in this cube I store a 1 if the mothvalue (h) between the start and end date.