Counting Days
I am having a difficult time in figuring out how to do this... Associates have their hotel length of stay recorded in two fields "Hotel Check-In Date" and "Hotel Check-Out Date" One of the functions...
Forum Discussion
Hans,
If I understand correctly, you have a report that needs to display all associates staying at a hotel in a given date range. In your example, an associate with a start and end date of 10/14 and 10/26 should show up in a report for associates staying in hotels on the dates 10/21 through 10/26 even though 10/21 is not explicitly outlined in this associate's stay.
I'm not sure if this can be done using the dynamic date range filter, as a formula field would need to reference a dynamic filter input. However, we may be able to think up something similar. Are there any business rules your user has that could be used to simplify the filter? Here are a couple of examples:
Example 1
Assumption:
Assumption:
Are there any ways you can think of to translate the business needs for the date range?
------------------------------
Evan Westbrook
PRIME Developer
Harder Mechanical Contractors Inc.
Portland OR
------------------------------
If I understand correctly, you have a report that needs to display all associates staying at a hotel in a given date range. In your example, an associate with a start and end date of 10/14 and 10/26 should show up in a report for associates staying in hotels on the dates 10/21 through 10/26 even though 10/21 is not explicitly outlined in this associate's stay.
I'm not sure if this can be done using the dynamic date range filter, as a formula field would need to reference a dynamic filter input. However, we may be able to think up something similar. Are there any business rules your user has that could be used to simplify the filter? Here are a couple of examples:
Example 1
Assumption:
- End user just wants to see all associates in a hotel in the last 7 days
- Create a checkbox-formula field that flags if the check out date is within the last seven days.
- Set this formula field as the dynamic filter instead of a date range
Assumption:
- End user just wants to view dates based on some business time unit (i.e. week ending date, billing period, etc.)
- Create a text-formula field that translates check in and check out date to the business time unit
- Set this formula field as the dynamic filter instead of a date range
Are there any ways you can think of to translate the business needs for the date range?
------------------------------
Evan Westbrook
PRIME Developer
Harder Mechanical Contractors Inc.
Portland OR
------------------------------
- Evan,
The 2nd example is closer to what is needed.
If I was going to lay it out it would be something like this...
Show me a count of nights for associates in hotel rooms from 10/21 thru 10/26 Would give me 6 nights for both Joe and Sally 14-Oct 15-Oct 16-Oct 17-Oct 18-Oct 19-Oct 20-Oct 21-Oct 22-Oct 23-Oct 24-Oct 25-Oct 26-Oct Joe X X X X X X X X X X X X X Sally X X X X X X Show me a count of nights for associates in hotel rooms from 10/14 thru 10/26 Would give me 13 nights for Joe 6 nights for Sally 14-Oct 15-Oct 16-Oct 17-Oct 18-Oct 19-Oct 20-Oct 21-Oct 22-Oct 23-Oct 24-Oct 25-Oct 26-Oct Joe X X X X X X X X X X X X X Sally X X X X X X
------------------------------
Hans Hamm
------------------------------- MarkShnier__You
Qrew Legend
Here is a thought.
If this is a low usage situation by very few users, who need this filter. Then make a single record in a new table called Analysis Dates. it will be Record ID# 1 and it will have two date fields for Analysis Start date and Analysis End Date.
Make a relationship down to your detail records. Now you can use these date fields to either filter records or to calculate the # of days within the hotel stay period which are within the Analysis Date range.
If you like, the records can be displayed directly on the Analysis date range record as an embedded report. But if not, I suggest that you display the Analysis Date range on the report itself as Group By headers or columns so the user knows what extra date filters are being used.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------- MarkShnier__You.. and if its multi user there is a solution for that too.
------------------------------
Mark Shnier (YQC)
Quick Base Solution Provider
Your Quick Base Coach
http://QuickBaseCoach.com
markshnier2@gmail.com
------------------------------
- Hans,
Mark has some good ideas below. I'm responding in a different branch of this thread just to keep our suggestions straight for you.
To accomplish what is mentioned in Example 2, you could create a library table called something like "Weeks" (or whatever your consistent time unit is.) Here's how you might set it up:
- Fields: [Week Description], [Week Start Date], and [Week End Date].
- Relate "Weeks" to "Hotel Stays" table one to many (one week can have many hotel stays.)
- Set up an automation to automatically link a [Hotel Check-In Date] [Hotel Check-Out Date] pair on "Hotel Stays" to the parent Weeks table. (See Brian's solution on response 3 for an example)
- Set up report on "Hotel Stays" table to filter based on "Week" for the hotel stay instead of the start and end date.
- i.e. "Show me the # of nights in Week Ending 10/20" instead of "Show me the # of nights between "10/14" and 10/20"
------------------------------
Evan Westbrook
PRIME Developer
Harder Mechanical Contractors Inc.
Portland OR
------------------------------
