
1. Re: Counting days in a month to calculate totals
Joshua Milligan Jun 13, 2013 5:34 AM (in response to Jerry Ward)Jerry,
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 (mockup data is great, just need to see and understand the structure).
Regards,
Joshua

2. Re: Re: Counting days in a month to calculate totals
Jerry Ward Jun 13, 2013 8:41 AM (in response to Joshua Milligan)Hi Joshua,
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?
Thanks,
Jerry

Mockup Data.xlsx 66.3 KB


3. Re: Counting days in a month to calculate totals
eduardo.couto Jun 13, 2013 10:21 AM (in response to Jerry Ward)Jerry
I use this formula to calculate days in a month.
rgrds
DATEDIFF( 'day',
MIN(
DATETRUNC( 'month', [DATA] )
) ,
MAX(
dateadd('day',1,
dateadd('month',1, DATETRUNC( 'month', [DATA] ) )
)
)
)+ 1

4. Re: Re: Re: Counting days in a month to calculate totals
Joshua Milligan Jun 13, 2013 11:40 AM (in response to Jerry Ward)Jerry,
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.
Regards,
Joshua

# Days.twbx.zip 30.8 KB


5. Re: Counting days in a month to calculate totals
Ben Shirley Jun 24, 2015 2:48 AM (in response to eduardo.couto) 
6. Re: Counting days in a month to calculate totals
Tableau kumar Jun 24, 2015 2:56 AM (in response to Ben Shirley) 
7. Re: Counting days in a month to calculate totals
Ben Shirley Jun 24, 2015 2:59 AM (in response to Tableau kumar)Thanks, Did anyone work out how to omit nonworking days?

8. Re: Counting days in a month to calculate totals
Anna Lytvyn Dec 15, 2015 11:12 AM (in response to Ben Shirley)HI, Ben
did you find the way how to deal with nonworking days?
I have the same question.
Thanks!

9. Re: Counting days in a month to calculate totals
Shawn Wallwork Dec 15, 2015 12:28 PM (in response to Anna Lytvyn)How do you define 'nonworking' days?
Shawn

10. Re: Counting days in a month to calculate totals
Ben Shirley Dec 16, 2015 3:17 AM (in response to Anna Lytvyn)1 of 1 people found this helpfulHi Ganna
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,
Ben

11. Re: Counting days in a month to calculate totals
Anna Lytvyn Dec 16, 2015 10:49 AM (in response to Ben Shirley)Hi Ben,
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('01jan2002'), rownum1 ) real_dt
from all_objects
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])
Thank you!

12. Re: Counting days in a month to calculate totals
Anna Lytvyn Dec 16, 2015 10:49 AM (in response to Shawn Wallwork)Hi Shawn,
I put my reply in next post.
thanks!