How to find date where cumulative sum reaches variable threshold using chart expression only?

07734 — Tue, 21 Oct 2025 18:31:44 GMT

Hello community,

I have two tables linked by the CAMPO field:

Table 1: CONTEO

Fields: CAMPO, INVERNADERO, FECHA_ALTA, FECHACORTE, etc.
Example: CAMPO="SONORA", FECHA_ALTA=08/08/2025

Table 2: Forecast_Clima_VAL

Fields: CAMPO, FECHA_CLIMA, VAL (daily numeric values)
Example data:

CAMPO	FECHA_CLIMA	VAL
SONORA	08/08/2025	1.5
SONORA	09/08/2025	1.0
SONORA	10/08/2025	2.0
...	...	...
SONORA	05/10/2025	1.5

Objective: I need to calculate the date when the cumulative sum of VAL reaches 80, starting from FECHA_ALTA of each record in CONTEO.

Expected example:

FECHA_ALTA: 08/08/2025
Cumulative sum: 1.5 → 2.5 → 4.5 → ... → 78.3 → 79.8 → 81.3
Result: 06/10/2025 (first date where cumulative ≥ 80)

What already works: This expression correctly sums VAL in a range:

qlik

Sum(
    Aggr(
        If(FECHA_CLIMA >= FECHA_ALTA AND FECHA_CLIMA <= FECHACORTE, VAL),
        FECHA_CLIMA, FECHA_ALTA, FECHACORTE
    )
)

This expression returns the total from FECHA_ALTA:

qlik

=Sum({<FECHA_CLIMA={">=$(=FECHA_ALTA)"}>} VAL)

Constraints:

I cannot modify the load script
I have a variable vVAL_Salida=80 (dynamic value, can change over time)

Question: Is it possible to calculate this with an expression in a chart?

I appreciate any guidance.

Re: How to find date where cumulative sum reaches variable threshold using chart expression only?

rubenmarin — Sat, 25 Oct 2025 11:49:04 GMT

Hi, I don't know what's the expected result, the example only has one CAMPO and INVERNADERO, if it helps.

If( Sum( Aggr( If(FECHA_CLIMA >= FECHA_ALTA AND FECHA_CLIMA <= FECHACORTE, VAL), FECHA_CLIMA, FECHA_ALTA, FECHACORTE ) )>60 and Sum( Aggr( If(Above(FECHA_CLIMA) >= Above(FECHA_ALTA) AND Above(FECHA_CLIMA) <= Above(FECHACORTE), Above(VAL)), FECHA_CLIMA, FECHA_ALTA, FECHACORTE ) )<60 ,FECHACORTE)

Will write the date when the accumulate changes.

If there is a fixed formula since FECHA_ALTA, it will be better the have precalculated values using the script, that would make the measures a lot easier to calculate.

topic Re: How to find date where cumulative sum reaches variable threshold using chart expression only? in Qlik Answers

How to find date where cumulative sum reaches variable threshold using chart expression only?

Re: How to find date where cumulative sum reaches variable threshold using chart expression only?