11 Replies Latest reply on Nov 26, 2018 12:39 PM by Daniel Stanish

# Averages by time frame

Hello,

I would like to create worksheets with start and end date filters, that will give the average number of Patients per day, week, month, year.  I'd like the average for the entire time frame, for example if they pick Jan - June the average per day is 50. Just one number on the worksheet.

• ###### 1. Re: Averages by time frame

Can you please provide your packaged workbook with (sample) data?

• ###### 2. Re: Averages by time frame

Good day Alyssa.

Clarifying, are you saying patients typically stay more than one day, and you want to know how many are "active" on a particular day? Or do you want to know, how many patients arrived on average on any given day in <time-period>? Or something else?

If it's the arrival case, then you could possibly do a calculation like:

SUM([Number of Records]) / DATEDIFF('day', <date range start>,<date range end>)

The date range start end could be parameters that you've built into filters, or a window function, e.g. WINDOW_MIN(MIN([entry date])), WINDOW_MAX(MAX([entry date])).

Does this help?

-Dan

• ###### 3. Re: Averages by time frame

Yes, I think that helps.  But if the date range is just a filter on a field called Service Date.  How would I put that into the calculation? Or should I make parameters?

• ###### 4. Re: Averages by time frame

Using parameters should work. However, it should be possible to use WINDOW_MIN(MIN([Service Date])) and WINDOW_MAX(MAX([Service Date])) to capture filtered the start and end dates. I'd suggest using that, since it will be more flexible than using parameters, and the date filters prevent end-date-before-begin-date awkwardness. So I would try:

SUM([Number of Records]) / DATEDIFF('day',WINDOW_MIN(MIN([Service Date])), WINDOW_MAX(MAX([Service Date]))))

Best regards,

-Dan

• ###### 5. Re: Averages by time frame

Thank you Daniel I will try it!!!!

• ###### 6. Re: Averages by time frame

My results are off by one day.  For example, I am finding the average of January, so that would be divided by 31 days, but the results of my formula below are if divided by 30 days:

SUM([Current RVU Amount]) / DATEDIFF('day', WINDOW_MIN(MIN([Service Date])), WINDOW_MAX(MAX([Service Date])))

• ###### 7. Re: Averages by time frame

Good day Alyssa.

Ah, right, fencepost error. DATEDIFF doesn't consider the whole last day when counting. To correct for this add one to the DATEDIFF result:

SUM([Current RVU Amount]) / (1+DATEDIFF('day', WINDOW_MIN(MIN([Service Date])), WINDOW_MAX(MAX([Service Date]))))

Does this help?

-Dan

• ###### 8. Re: Averages by time frame

Yes, that does help.  So that worked out...what if I was looking for average per month, in my example it is 4 total months June - Sept and the average is not dividing by 4 but by 2.9955.....

SUM([Current RVU Amount]) / DATEDIFF('month', WINDOW_MIN(MIN([Service Date])), WINDOW_MAX(MAX([Service Date])))

• ###### 9. Re: Averages by time frame

Good day Alyssa.

You still will have the "fencepost" issue with months as well as days. Modifying the denominator by adding one should resolve it. So

SUM([Current RVU Amount]) / (1+DATEDIFF('month', WINDOW_MIN(MIN([Service Date])), WINDOW_MAX(MAX([Service Date]))))

-Dan

• ###### 10. Re: Averages by time frame

The adding 1 made it closer, but not exact.  Now it is dividing by 3.99424... instead of 4.

• ###### 11. Re: Averages by time frame

Good day Alyssa.

Not sure why the denominator is a real number instead of an int. Can you post a sample workbook? In Tableau Desktop 2018.2, the equation (1+DATEDIFF('month', WINDOW_MIN(MIN([DateTime])), WINDOW_MAX(MAX([DateTime])))) returns a whole number, not fractional, even if the datetime is not on the end of a month, or that the day is partial.

-Dan