NETWORKDAYS formula

Hi Yaejis,

You can use the Networkdays function but depending on a month’s end date, it’s not the most accurate way to get what you need.

So here is how to do a count of your net work days using boolean properties.

STEP 1

Turn your Holiday Schedule Dimension into a ‘Holidays’ boolean property in the Day dimension.

- create a boolean property and use the formula

if(Day.'Start Date'='Holiday schedule'.date, true)[remove firstnonblank:'Holiday schedule']

We tell Pigment to look for the date that are considered Holidays and return true.

STEP 2

Add a Working Days boolean property to your Day of Week dimension

- create a boolean property and manually tick the working days

STEP 3

Create a ‘Net work days’ boolean property in the Day dimension.

- create a boolean property and use the formula

if(isblank(Day.Holidays) and Day.'Day of Week'.'Working days',true)

We tell Pigment to look for the dates that are not holidays and are considered working days, and return true.

That should return you a true boolean on each net work day.

STEP 4

Create your networkdays metric

- create a new metric structured by Month and using the formula:

If(Day.'Net work days', 1) [by sum: Day.Month]

You tell Pigment to sum the net work days using each Day’s Net work day boolean property you just created.

Hope it helps.