1 Reply Latest reply on Jan 14, 2014 1:31 PM by Jonathan Drummey

    Complex Rolling Averages Calculation

    Kevin Sullivan

      I want to create one calc that will still allow me to go up and down the date hierarchy from month up to year and give me a sales rate by different levels of date groupings


      When looking at the individual months level it should Calc -  Sum([Sold]) in the selected month / Avg inventory of current month and the last month

           For example February 2013 total sold of 100 would be divided by the inventory average of January 2013 (500) and February 2013 (600)   = 100/550 = 18.2%


      When rolled up to quarters calc should be Sum([Sold]) in the quarter / avg of inventory for 4 months.  1 month prior to quarter and the quarter months

           For example Q3 2012 would be sum sold july-sept and divided by avg inventory for June-Sept


      When rolled up to year the calc should be Sum sold  in the year divided by the December rolling 12 months including december.

           For example it would be sum sold jan- dec 2013 divided by avg inventory Dec 2012 - Dec 2013


      I would like one formula to handle all this so i can look at monthly, quarterly, and yearly rates by using the date hierarchy.

        • 1. Re: Complex Rolling Averages Calculation
          Jonathan Drummey

          Hi Kevin,


          I did a bunch of testing this past summer on this very issue and I can't figure out a way to do it using Tableau's built-in hierarchies. Fundamentally, the problem is that table calcs and hierarchies don't know anything about each other. This shows up in at least three ways in Tableau:


          1) We need to change the offsets/window for the calculations as the hierarchy changes, however we don't have an easy way to figure out what hierarchy level is currently active. I figured out how to do this using a self-blend for each level of the the hierarchy, however...


          2) When we expand a hierarchy, the dimension added by the hierarchy is automatically added to the partitioning of all table calculations in the view. This is diametrically opposed to what we want, which would be to add that dimension to addressing (along with changing the offsets/window).


          3) If we set a table calculation to address on dimensions that are in the view that are removed (for example by collapsing a hierarchy), the table calculation will fail. In some cases we could get away with relative addressing such as Table (Across), etc. but that will not work for all views. Nested table calculations with custom addressing inside IF/THEN/ELSE statements won't work here either, as soon as we bring the calc into a view Tableau will be looking to see whether all the dimensions used by the nested calcs are in the view.


          The workaround that I know of is to avoid Tableau's hierarchies entirely and use a parameter to either swap worksheets or use the parameter to swap out a dimension(s) as well as change the offsets.