12 Replies Latest reply on Dec 16, 2015 10:49 AM by Anna Lytvyn

# Counting days in a month to calculate totals

I am trying to build a calculation that will sum how many days have gone through in a month times a number.

I have done DAY(#Date Table#) and then change it to an attribute.  When I break down by day of month it gives me the day.  But when I try for the month it does not sum.

Example:

Each day we sell 20 items, so its the number of days that have gone by times 20.

Expanded by day:

January

1  2  3  4  5  6  7  8  9  10  11  12  13  14  15  16  17  18  19  20  21  22  23  24  25  26  27  28  29  30  31

1*20 = 20

2*20 = 40

When I want to show just months its aggregating them all together instead of taking 31* 20 = 620 for January

Jerry

• ###### 1. Re: Counting days in a month to calculate totals

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 (mock-up data is great, just need to see and understand the structure).

Regards,

Joshua

• ###### 2. Re: Re: Counting days in a month to calculate totals

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

• ###### 3. Re: Counting days in a month to calculate totals

Jerry

I use this formula to calculate days in a month.

rgrds

DATEDIFF( 'day',

MIN(

DATETRUNC( 'month', [DATA] )

) ,

MAX(

dateadd('month',1, DATETRUNC( 'month', [DATA] ) )

)

)

)+ 1

• ###### 4. Re: Re: Re: Counting days in a month to calculate totals

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

1 of 1 people found this helpful
• ###### 5. Re: Counting days in a month to calculate totals

That is a really handy little Calc. Thank you!!!

• ###### 6. Re: Counting days in a month to calculate totals

You can find the solution for this @ | Tableau Public

Best Regards

Laxman Kumar

• ###### 7. Re: Counting days in a month to calculate totals

Thanks, Did anyone work out how to omit non-working days?

• ###### 8. Re: Counting days in a month to calculate totals

HI, Ben

did you find the way how to deal with non-working days?

I have the same question.

Thanks!

• ###### 9. Re: Counting days in a month to calculate totals

How do you define 'non-working' days?

--Shawn

• ###### 10. Re: Counting days in a month to calculate totals

Hi 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

1 of 1 people found this helpful
• ###### 11. Re: Counting days in a month to calculate totals

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('01-jan-2002'), rownum-1 ) 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

Hi Shawn,

I put my reply in next post.

thanks!