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.
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.
Sounds like your filter is a table calc. Is it?
A sample workbook would really help here.
I am attaching a sample workbook. Please see the "Daily Productivity" dashboard and Sheet14.
Thanks for any help you can provide.
OK, I got it.
OK, help me out here. It's a busy sheet. Which row in which mark and what calc are you concerned about?
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)
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.
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)).
Help me out. Is that [Recommended Adj]?
What should the numbers be? What other row is that based on?
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.
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.
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.