COUNT by Business Days of week

Good afternoon,

I am new to birst and trying to learn BQL expressions. I have a table setup and each column is a different expression. One of my expressions needs to count the the number of job orders that occurred yesterday, but does not include Saturday or Sundays. Here is my expression listed below, but I am getting zero results from the column. If I remove the AND DatePart(DayOfWeek,[Jobs.Job Last Transaction Date]) <> 1 OR 7, i get results but it's obviously not filtering out Saturday or Sunday's.

([Job Date: # Distinct Job] WHERE [Jobs.Job Last Transaction Date] = DateAdd(Day,-1,nowdate)) AND DatePart(DayOfWeek,[Jobs.Job Last Transaction Date]) <> 1 OR 7

Any ideas on the proper syntax?

thank you,

Find more posts tagged with

Birst - General Discussions

Comments

Dan Rosen

You can try not in (1,7), or you can do [col] <> 1 or [col] <> 7

Diane McElhiney

If you are proceeding with a lot of date manipulation on Job Last Transaction Date, a better approach is in the modeling. Change Job Last Transaction Date to enable 'Analyze By Date' (type = Both). Birst associates these dates to the Time hierarchy. Within the hierarchy are many components to use in filtering directly in a visualization.

Legacy Contributor

I suggest adding a Custom Attribute to your Jobs dimension to store the Day of Week. It would make this much easier. In Space Management, in Custom Attributes, create a new custom attribute associated with the Jobs dimension. I would probably call it Job Day of Week. The expression would be something like:

DatePart(DayOfWeek,[Job Last Transaction Date])

Then you could do:

([Job Date: # Distinct Job] WHERE [Jobs.Job Last Transaction Date] = DateAdd(Day,-1,nowdate)) AND [Jobs.Job Day of Week]) not in (1, 7)

Diane McElhiney

To expand on Cindy's point, you can add this custom attribute to the Birst Time hierarchy (Space Management/Custom Attributes & Measures) by replacing Job Last Transaction Date with Date. This method opens the possibilities to use this attribute on any date associated to the Time hierarchy through Analyze By Date.

Legacy Contributor

Yeah, I was also wondering if Job Last Transaction Date is an analyze by date. Because it's not the same date used in the measure, so any use of the Time dimension would be on Job Date instead of Job Last Transaction Date.

Copyright © 2025 Infor. All rights reserved.