topic Re: Estimated completion date expression in front end in QlikView

Estimated completion date expression in front end

Wed, 12 Oct 2016 17:56:28 GMT

Input variable	Value
vNumberOfSteps	1500
vStartDate	10/19/2016

Pace	Pace Available on Date
120	10/17/2016
40	10/22/2016
25	10/27/2016

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.

Thank you

Re: Estimated completion date expression in front end

Oleg_Troyansky — Wed, 12 Oct 2016 18:49:09 GMT

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):

MIN(

AGGR(

IF(Rangesum( Above(sum( {<Date={">=$(vStartDate)"}>} DailyPace), 0 9999) >= $(vNumberOfSteps), Date)

, Date)

)

Cheers,

Oleg Troyansky

Learn advanced development techniques for QlikView and Qlik Sense in my book QlikView Your Business.

Re: Estimated completion date expression in front end

Wed, 12 Oct 2016 19:19:39 GMT

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!