4 Replies Latest reply on Nov 3, 2016 2:06 PM by Derek Wong

    Last Year Running Sum

    Derek Wong

      Hello! I've got a fun little problem I was hoping you all could help with.

       

      I want to create a calculation that is a running sum of last year's sales. I was able to grab last year's sales number using the LOOKUP function, but am lost on how to apply a running_sum in addition to the lookup function. I tried wrapping the calculation in various ways --i.e.  LOOKUP(RUNNING_SUM(SUM([Sales])),1) --but to no avail.

       

      I've attached a packaged workbook that shows the issue. The image below shows my progress, where "Last Year Sales" is grabbing [Sales] from the previous year. When functioning correctly, this will grab the "running sum of sales" value from the previous year. Any help is appreciated!

        • 1. Re: Last Year Running Sum
          Ivan Young

          Hi Derek,

          I believe the trick to getting this output is to create a table calc which you'll nest in another table calc.  This will allow you to have 2 different compute settings which was required (at least by me) to produce the view you are looking for.  I've attached a twbx which I think you can follow but I'm going to provide a brief explanation of what I did.

           

          1.  Create a lookup field for last years sales.  Lookup Sales - 1:  LOOKUP(SUM([Sales]),-1)

           

          2.  Create your running SUM.  LY Running Sales: RUNNING_SUM([Lookup Sales -1])

           

          3.  Drag you measure to the view and set the following compute settings.

           

           

           

           

          Let me know if you have any questions.

           

          Regards,

          Ivan

          1 of 1 people found this helpful
          • 2. Re: Last Year Running Sum
            Derek Wong

            Hi Ivan! Thanks for your reply.

             

            So your "two calculations" method is one that I've been working through the past few days, but am getting stuck when applying this method to the intended table format (i.e. days of month along the columns). Your example works with a default "Table (across)" calculation on Sum(Sales), but when I need a non-default calculation on Sales like "Table (down)", the second calculation for Running Sales falls apart because it is calculating with the wrong method.

             

            When I try to edit the Sales default calculation, I can't seem to force it to Table (down), even when fiddling with the Advanced options. I think the main reason for this is the inability to select the granularity of Date (i.e. Month(Date) or Year(Date) as opposed to just [Date])

             

            So the main question now is this: How do I force a table calculation's "compute using" setting to be an inherent property of the calculation, so that a secondary calculation that references the first will use the correct setting?

             

            Please let me know if I can clarify anything I've said thus far. Thanks!

            • 3. Re: Last Year Running Sum
              Ivan Young

              Hi Derek

              I'm not sure I'm entirely clear on what you are trying to do.  I don't often use the Default Table Calculation settings, I generally keep that setting at automatic and do my compute once the table calc has been added to the view.  If you set the default table calc to compute on a dimension that is not in the view it won't work anyway.

               

               

               

              Attached is a copy that shows last years running sum by day of month in columns.  If you are trying to get a different output let me know.

               

              Regards,

              Ivan

              • 4. Re: Last Year Running Sum
                Derek Wong

                After some fiddling I got the first answer to work for me! Thanks for your help, Ivan.