3 Replies Latest reply on Jul 19, 2018 7:28 AM by Brendan van Rijn

    Rolling Twelve Months calculation without using table calc

    Brendan van Rijn

      Hi all,

       

      I know this question has passed by a lot, though I can't seem to find a workable solution. Hope you can!

       

      What I want is a rolling twelve months calculation based on actual dates. In my case, this means I can't use a WINDOW_SUM table calc because not all dates are present in my table. For instance, in the screenshot below I'd like to see a last twelve months calculation in december 2016, calculating the sum of turnover from December 2016 back till January 2016 (e.g. complete 2016). But because there's no data in December, the table calc is adding the value from December 2015, because that's twelve rows.

       

      Rolling_L12M_calc.png

       

      I've tried several other posts suggestions with DATEADD and DATEDIFF but none of them helped my case. Specifically, I couldn't get the DATEDIFF calculation to always calculate the date minus 12 months with a dynamic anchor date/start date (without using filters or a table calc.).

       

      Ultimately I'd like to use the LTM measure in a map with date on the Pages shelf, so preferably I'd shy away from a table calc and go with a regular calc? I've added a simplified workbook that shows the above data and calculation.

       

      I've been stuck on this for a while now and would really appreciate your help on this!

       

      Running Tableau 2018.1.0

        • 1. Re: Rolling Twelve Months calculation without using table calc
          Zhouyi Zhang

          Hi, Brendan

           

          Please see my workaround, but still need to use the table calculation (I don't think there is way using just calculation to get last 12 month rolling sum)

           

           

          Hope this helps

           

          ZZ

          1 of 1 people found this helpful
          • 2. Re: Rolling Twelve Months calculation without using table calc
            Alex Kerin

            The only other way I can see to do this and not pad using missing values is this horror show of a table calc where we explicitly test datediff:

             

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-11),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-11)) else 0  END +

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-10),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-10)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-9),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-9)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-8),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-8)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-7),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-7)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-6),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-6)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-5),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-5)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-4),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-4)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-3),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-3)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-2),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-2)) else 0 END+

            if datediff('month',lookup(attr(DATEtrunc('month',[Date])),-1),attr(DATEtrunc('month',[Date])))<=11 then zn(LOOKUP(sum([Turnover]),-1)) else 0 END

            +zn(LOOKUP(sum([Turnover]),0))

            • 3. Re: Rolling Twelve Months calculation without using table calc
              Brendan van Rijn

              Thanks Alex! It really did help a lot. Just have to find a way to bypass the empty rows, as those are ignored, unfortunately