Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

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!

3 Replies
ahaahaaha
Honored Contributor

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

dineshraj
Contributor

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

mato32188
Contributor III

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

Community Browser