The wealth of knowledge here is amazing. I have found answers to a lot of questions and good tips and tricks. I have searched the board and can’t seem to find the answer to the following. Our business model sells 7 days a week, but only builds 5 days a week.
Sales is sales
Building equals revenue and is Monday through Friday.
I have figured out a more or less manual way to determine the # of business days in a month, but if today is the 8th of February how can I determine the number of build days left in the month where a build day is Monday through Friday?
All thoughts are welcome. Thanks in advance for the help
I’m a bit of a novice at this, and you may already have a reply, but here it goes. Note that I was trying to find the % Working MTD, but the logic is similar. Also, I was able to work with a universe that gave me a Current Month Y/N flag, which I placed a report filter on to get only the current month’s dates - but you could build a check for this if it’s not in your universe.
Firstly, I created a variable to understand the weekdays:
Workdays:
Then I created a variable to tell me how many days in the past:
Past Days:
=If([Calendar Date]<CurrentDate();1;0)
Then I checked to see if the day was both in the past, and a work day:
Working Days Past:
=If([Past Days]+[Work Days]=2;1;0)
I was then looking for a % so I calculated:
% Working Day Past
=(Sum([Working Days Past]))/Sum([Work Days])
but if you just wanted a sum of past working days, then just:
=(Sum([Working Days Past])
As I mentioned, I had a report filter that filtered the result by the Current Month, but you could build a check yourself for this. Let me know if this helps.
Actually, we’ve talked about this kind of thing many times on BOB. There is no way, using SQL, or Web Intelligence functions, to do any kind of calculation based on Business Days. Think about it. What is a business day? Usually Monday - Friday, minus holidays. The problem is that SQL, or report functions, don’t know what is a holiday for your company.
So, how do you do this calculation? Create a calendar table in the database, and add it to the universe. Then, all kinds of calendar calculations become, not only possible, but quite simple.
(BOB member since 2006-10-06)
(BOB member since 2002-08-08)
(BOB member since 2005-04-15)