1 2 Previous Next 17 Replies Latest reply on Jun 27, 2018 3:30 PM by Brad Kunst

    Need running sum to only sum filtered items in view

    Brad Kunst

      I created a view to show a daily summary for each day during a 14-day pay period.  The top part of the view shows the summary for each day and the bottom part shows cumulative totals through each day.  This works great without filters.

       

      I now want to filter the days and only want the cumulative values to sum up for the dates displayed on the view.  The running total table calculations do not work with filters.  I need a solution that will give me the running sum for only the filtered data.

       

      Below is a screenshot of my view for reference.  Notice that the Volume Quantity (top row) for June 3rd and June 4th totals 5,963.00 and is shown in the 3rd row within the CUMULATIVE section for June 4th.

       

      Thanks for any help you can provide.  I think it would a lot of work for me to provide access to the workbook because of the amount of data and sensitivity of the data.

       

       

      Regards,

       

      Brad Kunst

       

       

       

      Message was edited by: Brad Kunst

        • 1. Re: Need running sum to only sum filtered items in view
          Jeevan Krishna

          Hi Brad,

           

          I don't see any problem in your table calculation .

           

          Also table calculations will get affected by data in the view and changes when the data gets filtered. This is the intended behaviour.

          • 2. Re: Need running sum to only sum filtered items in view
            Brad Kunst

            The view I showed is correct because it does not contain any filters.  The view becomes incorrect when I filter on the date.  For example, in the filtered view below I only have 2 dates.  The CUMULATIVE volume quantity is showing a running total for ALL dates in my data source and displaying for June 10th.  Then on June 11th you see that the 158.00 volume for that day is added to the CUMULATIVE which becomes 4,890.00 (4,732.00 + 158.00).  I want the CUMULATIVE to show 148.00 and 306.00 for June 10th and June 11th respectively.

             

            Thanks for taking a look.

             

             

            Brad

             

            • 3. Re: Need running sum to only sum filtered items in view
              Joe Oppelt

              Sounds like your filter is a table calc.  Is it?

               

              A sample workbook would really help here.

              • 4. Re: Need running sum to only sum filtered items in view
                Brad Kunst

                I am attaching a sample workbook.  Please see the "Daily Productivity" dashboard and Sheet14.

                 

                 

                Thanks for any help you can provide.

                 

                Brad

                • 5. Re: Need running sum to only sum filtered items in view
                  Joe Oppelt

                  To attach something, click "advanced Editor" in the upper right corner on your reply:

                   


                  Then when you are in the advanced editor, click ATTACH in the lower right corner:

                   

                   

                  • 7. Re: Need running sum to only sum filtered items in view
                    Joe Oppelt

                    OK, help me out here.  It's a busy sheet.  Which row in which mark and what calc are you concerned about?

                    • 8. Re: Need running sum to only sum filtered items in view
                      Brad Kunst

                      If you can help me with rows 18 and 20 I think I can figure out the rest.

                      • ATTR(Total Target Hours)
                      • ATTR(Volume Quantity)

                       

                      I would like Mon Jun 11 to show the following in the cummulative section for each:

                      • Target Hours: 307.37 (138.15 + 169.22)
                      • Volume Quantity: 5,953 (2,646 + 3,307)

                       

                      Thanks

                       

                      Brad

                      • 9. Re: Need running sum to only sum filtered items in view
                        Joe Oppelt

                        See attached.  I did row 20.  (though I tossed out a bunch of the rows so that I could get the display to show my number with yours, and to make this run a little faster while I worked on it.)

                         

                        The key was making my own table calc, and within the tale calc, filtering out [Current Payroll] = False.

                         

                        That table calc is set to evaluate the same way you have it set for your quick table calc, but also I had to set the nested calc (Current Payroll) to evaluate the way it is set in the filter.  You can set one calc to go one way, and a nested calc to go another, like I did here.

                        • 10. Re: Need running sum to only sum filtered items in view
                          Brad Kunst

                          Joe,

                           

                          You have been very helpful and responsive.  Based on your example I was able to figure out all rows except the last row in your smaller example (field (13)).

                           

                          Thanks!!!

                           

                          Brad

                          • 11. Re: Need running sum to only sum filtered items in view
                            Joe Oppelt

                            Help me out.  Is that [Recommended Adj]?


                            What should the numbers be?  What other row is that based on?

                            • 12. Re: Need running sum to only sum filtered items in view
                              Brad Kunst

                              Joe,

                               

                              I updated my sample book with the changes you suggested and attached it.  This shows all rows working on Sheet14 except the last one - Recommended Adj. (AGG(MIN(1) (25)).

                               

                              This sheet shows 1 - 14 days of the pay period depending on which day we are in.  In the second day of a pay period it shows the previous day so we only see 1 day.  On the last day of a pay period we would see days 1-13.

                               

                              The Recommended Adj. field is just a running difference of the Productive Variance divided by the number of days left in the pay period.  So for day 1 there are 13 days left so we divide by 13.  For day 5 there are 9 days left so we divide by 9.  I was using (14 - index()) to figure out my denominator, however, when using more than 14 days worth of data with a filter this no longer works.

                               

                              Correct Recommended Adj. values for the days is listed below.  I think what I really need is a way to get the number of columns displayed on the view.  Right now there are 8 days (June 10 - 17th).  There could be up to 14 days.

                               

                              The correct Recommended Adj. field values for each day are listed below:

                               

                              June 10: [Productive Variance - Running Sum] / 13 .... -49.11 / 13 = -3.778

                              June 11: [Productive Variance - Running Sum] / 12 .... -70.26 / 12 = -5.885

                              June 12: [Productive Variance - Running Sum] / 11 .... -78.33 / 11 = -7.121

                              June 13: [Productive Variance - Running Sum] / 10 .... -74.10 / 10 = -7.410

                              June 14: [Productive Variance - Running Sum] / 9  .... -79.05 / 9 = -8.783

                              June 15: [Productive Variance - Running Sum] / 8  .... -52.03 / 8 = -6.504

                              June 16: [Productive Variance - Running Sum] / 7  .... -87.82 / 7 = -12.545

                              June 17: [Productive Variance - Running Sum] / 6  .... -128.24 / 6 = -21.373

                               

                               

                              Thanks again for helping me with this.

                               

                              Brad

                              • 13. Re: Need running sum to only sum filtered items in view
                                Joe Oppelt

                                OK.  Several things here.

                                 

                                Your [Recommended Adj.] calc isn't using  [Productive Variance - Running Sum].  It's just using  [Productive Variance].  On Sheet 14 I displayed  [Productive Variance - Running Sum] and I set that table calc to the same setting as [Recommended Adj.]  (Note;  There is a nested table calc in  [Productive Variance - Running Sum], and that needs to have the same setting too.  When you are editing  [Productive Variance - Running Sum], there is a line for nested calcs, and the pulldown lets you get to it and set it.)

                                 

                                I also put my own calc [index] on the mark, and set it with the same settings.  Now you can see what the numbers are for each day.  Go to copy 2 of the sheet.  Here I replaced [Recommended Adj.] with my copy of the same.  I modified it to use  [Productive Variance - Running Sum], and to use [index].  I set its table calcs, and that of all the nested calcs, to the same setting.

                                 

                                One thing I need to understand.  Your text above expects the divisor to arrive at 13, 12, 11, ...  Where do those numbers come from?  You can see that [index] (and likewise, INDEX() in your calc) starts at 23 and increments.  That's because index values 1-throughg-22 are still in the underlying table but are not displayed on this sheet because of the [Current Payroll Range] table calc filter.  Are you expecting 13-through-1 because you are displaying the last 13 pay periods?  We can monkey with the numbers a different way, but I need to understand what you expect here and we can come up with a way to make the divisor 13-through-1.

                                 

                                Bottom line, at this point I have the left side of the equation there for you.  We just need to make the right side correct.

                                • 14. Re: Need running sum to only sum filtered items in view
                                  Joe Oppelt

                                  So I was thinking about this more.  If you are displaying the last x-many days, you can use a function called LAST().

                                   

                                  In the attached I made a calc that is simply LAST(), and I display it after INDEX in that last row.  LAST() tells you how many marks you are away from the last item in the series.  So you can see that the last day on the sheet has 0 for the value.  Next-to-last day has 1, etc.  With this function, if you want a value of 1 at the end of the series, (and 2 at the next-to-last, etc.) then you can use LAST()+1 as your denominator.

                                   

                                  If you want the value in the denominator to be based on how many days the current date is from today, then you can use the TODAY() function and DATEDIFF.

                                  1 2 Previous Next