I want be able to input different values of vNumberOfSteps, and input different vStartDates. Different paces are available at different start dates (chart 2). Unfortunately, the values in chart 2 are derived as expressions in a straight table, because I have filters that can change their values.
What I am looking to calculate, is the estimated end date for 1500 steps, if it starts on 10/19/2016, and the paces don't become available until the specified dates (see calculations below). Any ideas how to calculate this all in the front end? I can create a data island in the backend with all future dates if needed, but I don't want to associate pace or start date data to those dates in the script because I want filters to change them.
10/17/2016: 0
10/18/2016: 0
10/19/2016: 120
10/20/2016: 240
10/21/2016: 360
10/22/2016: 520 (360 + 120 + 40)
10/23/2016: 680
10/24/2016: 840
10/25/2016: 1000
10/26/2016: 1160
10/27/2016: 1345 (1160 + 120 + 40 + 25)
10/28/2016: 1530 (Complete because over 1500)
All I really care to see is 10/28/2016, it can be in a chart or in a text object, I am trying to avoid reloading the document to get this number.
I'd suggest creating a Calendar with a separate row per day and calculate the daily Pace there.
After that, you should be able to use the AGGR() function, in combination with Rangesum() and Above(), to calculate the cumulative balance of steps. Something along the following lines (can't guarantee 100% syntax accuracy):
Thank you Oleg. I am trying to avoid calculating the daily pace in the Calendar since I will allow dynamic selections that will change which date these paces become available.
That said, Rangesum(Above( ) ) might be the pieces I was missing in my formulas. I'll give those a read - thank you!
