Skip to main content

Hello, 

 

Can you please help me creating a formula to show net working days for each month?

The excel formula will be like this: 

So far, I created a dimension 

 

I referenced the article here but could not really understand what I should do to get list of working days for each month. 

 

Thank you!

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)tremove 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.


thank you!


Reply