Solved

NETWORKDAYS function

  • 3 November 2023
  • 3 replies
  • 138 views
A
Rank
Userlevel 2
Badge
  • Adam New Pigmenteer
  • New Pigmenteer
  • 4 replies

Hi, 

I struggle to understand how NETWORKDAYS function can be used. I read the help page for the function but in vain. 

I want to get number of working days in Jan 2023 (ignoring holidays). 

My formulas is:

NETWORKDAYS(date(2023,1,1),date(2023,1,31),'01. Day of week'.Working = true)

where Day of week is a dimension I created (below). 

I am getting 150 as the result of the formula which is obviously wrong. Help please :)

 

icon

Best answer by Issam Moalla 5 November 2023, 18:25

View original

3 replies

BrunoRodriguez
Rank
Userlevel 2
Badge +1

Hello @Adam ,

 

Networkdays is sometimes not the best option, but there are alternatives to achieve the same approach.At the end, the idea behind your requirement is the identification what days to subtract from the total number of days. This means the following:

 

  1. You need to create a metric where all the weekends and / or bank holidays are identified. This means that if you were to leverage the boolean that you created in the “Day of Week” dimension, every working day will be identified when you create a metric with the following formula.
'Day of Week'.Working[by: Day.'Day of Week']
  1. The next step is to count the amount of booleans per month, which can be done with the following formula - please note that the if statement is the exact formula above :
if('Day of Week'.Working[by: Day.'Day of Week'],1)[by SUM:Day.Month]

 

The above should lead you to the total number of days you are after. From here, you could start also thinking about bank holidays and how to account for them if required.

 

I hope the above helped! Please let us know if anything was not clear :)

Issam Moalla
Rank
Userlevel 3
Badge +1

Hi Adam,
 

Bruno provided a great alternative to your problem.

If you would like to use the NETWORKDAYS, I believe this formula needs to be applied using the native dimensions by Pigment instead of creating the dimension yourself.

1- First you need to adjust the calendar of your application or the application sharing the calendar by adding the Day dimension in the calendar settings.

Adding Day as time dimension

2- By saving the changes, the calendar would be updated and you would find the Day and Day of Week added to the calendar folder
 

Calendar folder

3- Add the property Working days to the Day of week Dimension like describe in the documentation

  • Working Days defines which days of the week are working and non working days. User must use a Metric (or property) of the Dimension Day Of Week ⇒ boolean(Which refers to the native dimension in Pigment)

Day of Week dimension


4- Apply your formula. I have changed the Date to argument to 01/02/2023 since this date is not taken into account and we need to include 31/01/2023.
 


Hope this provided more clarifications around the NETWORKDAYS formula.

 

A
Rank
Userlevel 2
Badge

Thanks Both. NETWORKDAYS eventually worked for me with the small modification suggested by Issam. 

Reply


Ask your question here!

Terms & ConditionsCookie settings