Re: Obtain Total for the Following Year - Qlik Community

pgalvezt2021

Hello !!!!

I have a requirement that I hope you can help me with.

What I want to achieve is to carry over the final total from 2025 in an Income and Expenses table, so that this total is reflected in the STA line when selecting 2026.

Ideally, this should work automatically for future years as well. For example, when 2027 becomes the new year, the final total from 2026 should automatically be carried over to the STA line for 2027.

What I have achieved so far is to carry over the 2025 final total to the STA line. However, when I move to 2026, the value changes, whereas I need the value to remain fixed based on the previous year's final total.

I have attached my current formula, which achieves the first part of the requirement, along with the variables and an Excel file for better understanding of the expected behavior.

If(
af_estado_cuenta_item = 'Egresos'
and af_estado_cuenta_concepto = 'Saldo temporada anterior',

Sum(Total
Aggr(
(
If(
af_estado_cuenta_item = 'Ingresos'
and af_estado_cuenta_descripcion_estado_ingresos <> 'R',

Pick(
Match(
af_estado_cuenta_concepto,
'Ingreso Cerezas',
'Ingreso Ciruelas',
'Ingreso Kiwis',
'Ingreso Manzanas'
),

Sum(
{<
af_especie={'Cerezas'},
af_estado_cuenta_concepto={'Ingreso Cerezas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum(
{<
af_especie={'Ciruelas'},
af_estado_cuenta_concepto={'Ingreso Ciruelas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum(
{<
af_especie={'Kiwis'},
af_estado_cuenta_concepto={'Ingreso Kiwis'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum({<af_temporada={2025}>}
{<
af_especie={'Manzanas'},
af_estado_cuenta_concepto={'Ingreso Manzanas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
)
),

If(
GetFieldSelections(rnp_tipo) = 'RNP Ppto',
Sum(af_estado_cuenta_rnp_ppto_monto),

If(
GetFieldSelections(rnp_tipo) = 'RNP Real Acum Ppto',
Sum(af_estado_cuenta_rnp_rap_monto),

If(
GetFieldSelections(rnp_tipo) = 'RNP Prom',
Sum(af_estado_cuenta_valor_total_ingreso_real)
)
)
)
)
),
af_estado_cuenta_item,
af_estado_cuenta_concepto
)
),
)

Variables:
$(v_cajasdn) = sum(if(af_estado_analisis_financiero_produccion = 'P',af_cajas_vigente_real,af_cajas_produccion_real))
$(v_rnp_Ingresos_I) = Avg(resultado_neto_productor_unitario_caja_NRNP)

Thank you!

Chanty4u

Hi

Use the previous selected year in the set analysis instead of fixing 2025.

Sum(

{<af_temporada={"=$(=Max(af_temporada)-1)"}>}

TOTAL Aggr(

YourExpression,

af_estado_cuenta_item,

af_estado_cuenta_concepto

)

For the STA line, replace the hardcoded:

af_temporada={2025}

With

af_temporada={"=$(=Only(af_temporada)-1)"}

pgalvezt2021

Hello Thank you for your reply !!!!

I have implemented the changes you suggested, but QlikView is still showing the 2026 Current Account Balance instead of the 2025 balance.

The balance shown when I select 2026 is the balance calculated from the script. The idea is to replace that value with the final total calculated when 2025 is selected.

In other words, the 2025 value should be frozen and carried over to 2026 specifically for that line.

Here is the script:

Sum(

{<af_temporada={"=$(=Max(af_temporada)-1)"}>}

TOTAL Aggr(

If(
af_estado_cuenta_item = 'Egresos'
and af_estado_cuenta_concepto = 'Saldo temporada anterior',

Sum(Total
Aggr(
(
If(
af_estado_cuenta_item = 'Ingresos'
and af_estado_cuenta_descripcion_estado_ingresos <> 'R',

Pick(
Match(
af_estado_cuenta_concepto,
'Ingreso Cerezas',
'Ingreso Ciruelas',
'Ingreso Kiwis',
'Ingreso Manzanas'
),

Sum(
{<af_temporada={"=$(=Only(af_temporada)-1)"},
af_especie={'Cerezas'},
af_estado_cuenta_concepto={'Ingreso Cerezas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum(
{<af_temporada={"=$(=Only(af_temporada)-1)"},
af_especie={'Ciruelas'},
af_estado_cuenta_concepto={'Ingreso Ciruelas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum(
{<af_temporada={"=$(=Only(af_temporada)-1)"},
af_especie={'Kiwis'},
af_estado_cuenta_concepto={'Ingreso Kiwis'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum({<af_temporada={"=$(=Only(af_temporada)-1)"},
af_especie={'Manzanas'},
af_estado_cuenta_concepto={'Ingreso Manzanas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
)
),

If(
GetFieldSelections(rnp_tipo) = 'RNP Ppto',
Sum({<af_temporada={"=$(=Only(af_temporada)-1)"}>}af_estado_cuenta_rnp_ppto_monto),

If(
GetFieldSelections(rnp_tipo) = 'RNP Real Acum Ppto',
Sum({<af_temporada={"=$(=Only(af_temporada)-1)"}>}af_estado_cuenta_rnp_rap_monto),

If(
GetFieldSelections(rnp_tipo) = 'RNP Prom',
Sum({<af_temporada={"=$(=Only(af_temporada)-1)"}>}af_estado_cuenta_valor_total_ingreso_real)
)
)
)
)
),
af_estado_cuenta_item,
af_estado_cuenta_concepto
)
),
)
,

af_estado_cuenta_item,

af_estado_cuenta_concepto

)

Thank you

Stefan764

short it

Hey,

Your Only(af_temporada)-1 is dynamic — it recalculates when you select 2026 instead of freezing the 2025 total.

Try this:

Replace:

{<af_temporada={"=$(=Only(af_temporada)-1)"}>}

With:

{<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"}>}

This forces it to always look at the max available year minus 1, not the current selection.

If that still doesn't work, you'll need to pre-calculate the prior year's closing balance in the load script and store it as a fixed value. Pull that instead of recalculating on the fly.

pgalvezt2021

Hi Stefan,

Thank you for your reply....

The value is still changing; I haven't found a way to keep it fixed. I'll share the formula as well so you can verify whether I implemented it correctly.

=Sum(

{<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"}>}

TOTAL Aggr(

If(
af_estado_cuenta_item = 'Egresos'
and af_estado_cuenta_concepto = 'Saldo temporada anterior',

Sum(Total
Aggr(
(
If(
af_estado_cuenta_item = 'Ingresos'
and af_estado_cuenta_descripcion_estado_ingresos <> 'R',

Pick(
Match(
af_estado_cuenta_concepto,
'Ingreso Cerezas',
'Ingreso Ciruelas',
'Ingreso Kiwis',
'Ingreso Manzanas'
),

Sum(
{<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"},
af_especie={'Cerezas'},
af_estado_cuenta_concepto={'Ingreso Cerezas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum(
{<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"},
af_especie={'Ciruelas'},
af_estado_cuenta_concepto={'Ingreso Ciruelas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum(
{<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"},
af_especie={'Kiwis'},
af_estado_cuenta_concepto={'Ingreso Kiwis'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
),

Sum({<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"},
af_especie={'Manzanas'},
af_estado_cuenta_concepto={'Ingreso Manzanas'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
)
),

If(
GetFieldSelections(rnp_tipo) = 'RNP Ppto',
Sum({<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"}>}af_estado_cuenta_rnp_ppto_monto),

If(
GetFieldSelections(rnp_tipo) = 'RNP Real Acum Ppto',
Sum({<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"}>}af_estado_cuenta_rnp_rap_monto),

If(
GetFieldSelections(rnp_tipo) = 'RNP Prom',
Sum({<af_temporada={"=$(=Max(TOTAL af_temporada)-1)"}>}af_estado_cuenta_valor_total_ingreso_real)
)
)
)
)
),
af_estado_cuenta_item,
af_estado_cuenta_concepto
)
),
)
,

af_estado_cuenta_item,

af_estado_cuenta_concepto

)

marcus_sommer

I suggest to reduce the complexity to focus on the issue - respecting unexpected on period-selections. This means removing all other conditions especially the nested if-loops and the pick(match()) branching.

It remains something like:

sum(aggr(sum(aggr(sum(aggr(sum/avg()))))))

Each of the parts may need the relevant set analysis condition respectively may look like:

sum({ SET } aggr({ SET } sum({ SET } aggr({ SET } sum({ SET } aggr({ SET } sum/avg({ SET } ))))))

Are there embedded aggregations within the set analysis they may also need an extra { SET }.

In my experience it's quite hard to develop such approach as a complete measure. Much simpler will be to use a table-chart and adding there all single parts in parallel measures and then combining them step by step.

Beside of the above such approach of nested aggr() requires a lot of resources and by a bit larger data-sets it will be probably quite slow. Therefore I would try to simplify everything possible, for example transferring the pick(match()) directly within the sum(), like:

...
Sum(
{<
~~af_especie={'Cerezas'},~~
af_estado_cuenta_concepto={'$(=maxstring(af_estado_cuenta_concepto))'}
>}
Aggr($(v_cajasdn) * $(v_rnp_Ingresos_I), af_variedad)
)
...

Obtain Total for the Following Year

Data Model

General Question

Script and Expressions

Variables

Visualization