Solved

NETWORKDAYS function

  • 3 November 2023
  • 5 replies
  • 273 views
A
Userlevel 2
Badge +1
  • Adam
  • Occasional Observer
  • 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

5 replies

BrunoRodriguez
Rank
Userlevel 3
Badge +6

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 5
Badge +9

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
Userlevel 2
Badge +1

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

Cat M
Badge +1

Hi all,

I had the same question as Adam and I found the answer from @Issam Moalla  above super helpful for how to set the working days per day of the week, especially the screenshots.

However, I’m still struggling to visualise how the NETWORKDAYS formula should look to also define and exclude the Holidays from the count. I checked the help page for NETWORKDAYS but can’t figure it out. Do I create a Holidays transaction list in my calendar with the holiday dates listed? And then how should it be written in the formula to include this?

Please let me know if better to create a separate topic for this question.

Many thanks,

Cat

CDALMAY
Rank
Userlevel 2
Badge +5

Hi @Cat M ,

I see 2 options in your case :

  1. Create a property “Holiday_Property” in the DAY dimension with. The data type is Boolean.
  2. Create a metric “Holiday_Metric” with only the DAY dimension, here also the data type is Boolean.

To populate either the property or the metric, as you mentioned, you can have a transaction list where you load only the day of the holidays

Transaction List to store all the holidays


and use the following formula :
 

Formula for the Holiday property or metric 

You can then leverage the formula by using :
NETWORKDAYS(start_date,end_date,’Day of Week’.Workday, Day.Holiday_Property)

or

NETWORKDAYS(start_date,end_date,’Day of Week’.Workday, Holiday_Metric)

With the holidays we have 20 days instead of 22
Without the holidays specified we have 22 days in Jan-23 (Saturday and Sunday defined as off days)

Let me know if that helps

Clement

Clement

Reply


Ask your question here!

Terms & ConditionsCookie settings