4 Replies Latest reply on Apr 7, 2014 3:45 PM by Joel Rosado

# how to calculate run rate numbers?

Hi, is it possible to create a dynamic forecast based on the current date and historical data?

The data set: number of visits on a daily basis - e.g.,

Date     Visits

1/1/12      80

1/2/12     108

1/3/12     95

...          ...

2/15/12     108

I would like to show the total number of visits for each month, and for the current month, show what the run rate total would be.  So for January it would just be summing all the historical visits I have, but in February, it would be summing all the Feb visits I have to date, dividing by 15 and then multiplying to 29 to get to the total.

I need this to automatically calculate as new data gets populated into the database so if the latest date is 2/15/12, then the run rate in Feb is (sum of visits in Feb)/15*29.  Tomorrow when I get 2/16/12 data, the run rate is then (sum of visits in feb)/16*29.  When we get to march, similar formula except that now I'm multiplying by 31 days (and Feb now is just the sum total of all the dates which I now have data for)

Display-wise, I'd love to see a bar chart of the historical monthly summary (e.g., Nov 2011, Dec 2011, Jan 2012) + the last bar being the run rate total for the current month (e.g., Feb 2012).

Is this possible in Tableau and how would I do this type of calculation?

Thanks!

• ###### 1. Re: how to calculate run rate numbers?

You basically just need to create some calculated fields to give you the total number of days in the month and the number of days to date.

I've put a little example together with some random data (because it's easier than explaining in detail).  Take a look at the calculations in the attached workbook. Note that I assumed that you don't have any missing days in the data, and that your data started at the start of a month.

I've done a couple of variants - the second one showing how to highlight the projection as opposed to the actuals. • ###### 2. Re: how to calculate run rate numbers?

Thanks for the quick reply Richard - this is exactly what I need.  One question though, is there something special you did to get the projection and actuals as stacked bars?  When I drag the projection onto the vertical axis, it creates another set of bars for the projections.  Thanks!

• ###### 3. Re: how to calculate run rate numbers?

I'm using "Measure Names" and "Measure Values" - which are special fields which allow you add multiple measures to an axis like this. Have a look at row, colour and filter shelves. If in doubt, have a look for "Measure Names" and "Measure Values" in the online help.

• ###### 4. Re: how to calculate run rate numbers?

How would you calculate run rate for a week, instead of for the entire month?