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'.Workingeby: Day.'Day of Week']

2. 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'.Workingeby: Day.'Day of Week'],1)oby 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 :)

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.

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
 

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)

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.

 

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

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

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

and use the following formula :
 

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)

Let me know if that helps

Clement