4 Replies Latest reply on Apr 18, 2018 8:33 AM by William George

    Excluding values from running sum total

    William George

      I feel like I've searched everywhere for this answer, and although it seems like I've tried everything, I haven't gotten this to work. Basically, I have accounts, and not all of them have made it a certain number of weeks. I want a running sum total of their values, but if an account doesn't have a value for that week, then I don't want their previous values in that calculation. The attached workbook should explain what I'm talking about. In Sheet 1 I want the running sum for week 3 to be 39, not 52, because account 456789 wasn't opened at week 3, and therefore I don't want to include them in the grand running total. I can't figure out how to do this when aggregating the accounts. Thanks very much.

        • 1. Re: Excluding values from running sum total
          Joe Oppelt

          There are several ways to do this.  The best way will depend on what you really want to do.

           

          Your data has 3 weeks in the sample workbook.  I assume you won't have just 3 weeks of data in your actual application. Therefore your user will probably have a way to indicate what weeks to display.  One way or another you will have a "last week to display", and that's probably the week you really want to look at for each account.  Is that true?  If so, will your "last week" always be the max week in our data?  Or (for example) if you have 52 weeks of data and the user asks for weeks 10-20, will week-20 be the week you want to examine for each account?


          And will there ever be breaks in the data?  For instance, could an account have week 1 and week 3 data, but not week 2 (in your example?)  If so, do you still want to count that account's values?

          • 2. Re: Excluding values from running sum total
            William George

            Thanks, Joe. It will always start with week 0, but yes, the max might change. There won't be breaks in the data.

            • 3. Re: Excluding values from running sum total
              Zhouyi Zhang

              Hi, William

               

              Please find my solution attached as well as below steps for reference.

               

              Result:

               

              Step 1: create two calculation fields

               

              Step 2: set up table calculation as below

               

               

              Hope this helps

               

              ZZ

              1 of 1 people found this helpful
              • 4. Re: Excluding values from running sum total
                William George

                This is fantastic. Thanks so much.

                 

                Is there any way to also show how many accounts are included in each week in the tooltip? For example, for week 0, 1, and 2, it would show 3, and for week 3, it would show 2. This isn't as important as the original question, but it would be nice to filter so that I could only get weeks that have a certain number of accounts that have made it that far, if that makes any sense. This is particularly relevant if I changed the running sum to a running sum of the average, and I don't want to include weeks with a small n.