3 Replies Latest reply: Mar 30, 2017 11:48 AM by Martin Baculik

# Calculate Date that reach a threshold value

Hi experts!

I would like to calculate the exact date in which the cumulative value of a field reach a certain point. How can i do it?

Example: i have a dataset in which for each day there are a certain number of values collected, like:

Date:          xxx

28 March     ***Text***

27 March     ***Text***

27 March     ***Text***

27 March     ***Text***

26 March     ***Text***

23 March     ***Text***

23 March     ***Text***

23 March     ***Text***

I would like to know which is the DATE_X in which the number of ***text*** from today till DATE_X is equal to 5. I cannot do that using the loadscript because the result has to be dependent to the filters present in the sheet.

Any help?

Thankyou very much!

• ###### Re: Calculate Date that reach a threshold value

Hi Andrea,

I tried to write down the selection information into a variable, and then from the script code use her. Unfortunately, the script gives an error at circulation to the calculated values of the variable (see the attached file). As variant, may be the information about the selections save to external file (xls, qvd, csv), and then use the information from this external file when loading script?

Regards,

Andrey

• ###### Re: Calculate Date that reach a threshold value

Hi Andrea,

I suggest you to use reference line in Add ons tab(if you use line chart or bar chart) for thershold value and Calculate the range sum

or

Use Combo chart with two measures. one is cumulative sum other is thershold value.

Thanks and Regards

• ###### Re: Calculate Date that reach a threshold value

Hi Andrea,

try this:

=FirstSortedValue(Date,aggr(if(rangesum(above(sum({<Date = {'>\$(=Today())'}>}Value),0,RowNo(Total)))=5, 1,2),Date))

BR

Martin