I think what you want is possible, but it would help to see the data you have. Do you have a packaged workbook you could share (mock-up data is great, just need to see and understand the structure).
I have attached some mockup data. The column Room Nights is the number of rooms sold for a hotel on the specific Stat Date.
I am trying to determine the occupancy by day ([Room Nights Total Per Day]/59) Where 59 equals the number of rooms available for each day.
When I want to summarize by month I need the total Room Nights (Specific Month) / (59 * Number of Days in the Month)
Year would be total Room Nights (Specific Year) / 59 * (Number of Days in the Year)
Let me know what you think I can do?
Mockup Data.xlsx 66.3 KB
I use this formula to calculate days in a month.
DATETRUNC( 'month', [DATA] )
dateadd('month',1, DATETRUNC( 'month', [DATA] ) )
Your data seems to include records for every day. This is very convenient as you should be able to use COUNTD([StatDate]) to get the distinct number of days for each month. It will actually work at any level of detail (Year, Quarter, Month, Day) provided you have a record for every day in the given period. For example, the attached workbook works at the month and day level because you have a record for every day in January and February. But it does not work at the Quarter or Year level because you don't have all the months of the year in the dataset.
If your dataset doesn't contain all the days (in which case, it is called sparse data), then there are other approaches to solving the problem. But having a record for every day certainly makes for the easiest approach.
# Days.twbx.zip 30.8 KB
Thanks, Did anyone work out how to omit non-working days?
did you find the way how to deal with non-working days?
I have the same question.
How do you define 'non-working' days?
1 of 1 people found this helpful
If you download the example workbook that Jerry shared, assuming your calc and data works in the same manner,
I resolved the count by dropping the DATE [StartDate] on the filters shelf, then choosing "WEEKDAY", then from there, choosing the days i wanted to include.
in the example workbook, i excluded Sat and Sun and the count for Jan dropped to 21, which in 2010 is correct.
Hope this helps,
thank you for taking look on this!
we have find out that to get the data drilled down by dates we need to create 'FAKE' rows:
select real_name, real_dt
from ( select distinct name real_name
from customer ),
( select add_months( to_date('01-jan-2002'), rownum-1 ) real_dt
where rownum <= 12 )
and then RIGHT OUTER JOIN to your data by real_name, real_dt.
# of End Days: COUNTD([END_DT])
# of Month Days: COUNTD([Fake Date])
I put my reply in next post.