5 Replies Latest reply on May 2, 2018 5:44 PM by Jennifer VonHagel

# Monthly total of all non-business days

Hi everybody,

I've been struggling for the last few days in trying to calculate all NON-business days in the current month.

Please note that the data source, other than including one row for each day of the month, also includes a weekend_YN field = ‘Y’ for weekends and holiday_YN = ‘Y’ for US holidays.

So after filtering the current month, I created a simple calculated field called Non_business_days that SHOULD return 1 if the day is either a weekend or an holiday, 0 otherwise, expecting the sum to be 9… instead of 30,346,912!!!

The calculated field for non_Business_days is:

if [Weekend Yn] = 'Y' or [Holiday Yn]='Y' then 1 else 0 END

Any help would be greatly appreciated!

Thanks.

• ###### 1. Re: Monthly total of all non-business days

Could you attach your book (**.twbx)?

Thanks,

Shin

• ###### 2. Re: Monthly total of all non-business days

Please provide the packaged workbook for someone to look into it and provide the appropriate solution.

• ###### 3. Re: Monthly total of all non-business days

It will help if you attach a workbook, but it sounds like you are counting records in your data, you need to count unique dates.

COUNTD(if [Weekend Yn] = 'Y' or [Holiday Yn]='Y' then [date_field] else NULL END)

Thanks,

Jennnifer

• ###### 4. Re: Monthly total of all non-business days

That's it! I've been trying to build it for a couple of days and I was almost losing my mind!

It was easy after all... yeah... after somebody did it for me!

Thanks Jennifer, much appreciated.

Roberto

• ###### 5. Re: Monthly total of all non-business days