1 Reply Latest reply on Nov 5, 2012 7:21 AM by Alex Kerin

    Common Calendar Calculations / Trending Calculations HELP

    kyle.young

      Hello All -

       

      I'm trying to create some cohort date calculations for my CallVolume. I want my calculations to update automatically when I open the workbook daily. ( not sure if this is possible if I will have to go in and edit or filter the data manually ) My goal is for upper management to open the workbook and not have to filter, or click on anything.

       

      Cohort Daily Calculations:

      Calc1 : CallVolume for yesterday. This one works.  =IF [Date]=today()-1 Then [CallVolume] End

      Calc2:  Take the 6 day avg of yesterdays CallVolume . Example, if yesterday was Sat then I want the formula to look at the last 6 Sat and take the CallVolume avg

       

      Cohort Weekly Calculations:

      Calc3: Sum CallVolume for last week (Mon thru Sun)

      Calc4: prior 6 wks CallVolume avg

      Calc5: Current Week to Date CallVolume

       

      Cohort Monthly Calculations:

      Calc6: Sum MTD CallVolume.

      Calc7: Prior 3 Month Avg CallVolume

      Calc8: Same Month last year CallVolume

      CalcMonthlyTrending 9: I want this formula to look at yesterdays date, days in month, days remaining and calculate a call trend for remainder of month.

       

      Any help would be appreciated. Thanks.

       


        • 1. Re: Common Calendar Calculations / Trending Calculations HELP
          Alex Kerin

          Attached. An interesting array of time calcs - you have to be careful what you use to make sure the turn of the year does not screw it up.

           

          I used a parameter [Other Date] instead of today() in the calculations - this allows for testing and the fact you have no data for yesterday. You can use the other parameter to switch between today() or the other date.

           

          I also used "Then [Date]" as the result of the if statements. This returns a valid list of dates that you can use as a filter (just exclude "Null). You could swap all of the [date] to [call volume] to return a number.

           

          For averages, use the date as a filter and change the aggregation to avg...

           

          The likely trend is interesting - your call volume is highly dependent on weekday - A Monday is 5 times higher on average than a Sunday, so prior day volume will not be a good predictor of the remainder of the month.

           

          However, the calculation I created was: take the volume for the day prior, times by the number of days left in the month from today, and add whatever days in the month we do have a volume for.

           

          You may want to think about using an average for the month so far, but even that will be skewed early in the month...

           

          The only other slightly confusing issue is that Tableau's week starts Sunday - yours starts Monday, so I had to account for that. Please check all work, especially behavior around year change and Sunday/Mondays....