
1. Re: Need running sum to only sum filtered items in view
Jeevan Krishna Jun 14, 2018 12:33 PM (in response to Brad Kunst)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 Jun 14, 2018 1:08 PM (in response to Jeevan Krishna)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 Jun 14, 2018 1:16 PM (in response to Brad Kunst)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 Jun 18, 2018 2:45 PM (in response to 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 Jun 18, 2018 2:48 PM (in response to Brad Kunst) 
6. Re: Need running sum to only sum filtered items in view
Joe Oppelt Jun 18, 2018 2:49 PM (in response to Joe Oppelt)OK, I got it.

7. Re: Need running sum to only sum filtered items in view
Joe Oppelt Jun 18, 2018 2:54 PM (in response to 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 Jun 18, 2018 3:02 PM (in response to Joe Oppelt)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 Jun 18, 2018 3:21 PM (in response to 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 Jun 20, 2018 4:41 PM (in response to Joe Oppelt)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 Jun 21, 2018 8:37 AM (in response to Brad Kunst)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 Jun 22, 2018 2:38 PM (in response to Joe Oppelt)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 113.
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 Jun 23, 2018 9:09 PM (in response to Brad Kunst)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 1throughg22 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 13through1 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 13through1.
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 Jun 25, 2018 7:49 AM (in response to Joe Oppelt)So I was thinking about this more. If you are displaying the last xmany 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. Nexttolast day has 1, etc. With this function, if you want a value of 1 at the end of the series, (and 2 at the nexttolast, 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.