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

[07734]

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:

1. I cannot modify the load script
2. 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.

[rubenmarin]

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.