Build an accumulated metric in a straight table based on multiple dimensions

[charliota]

Hello everyone,

I’m having trouble building a KPI in a straight table, since I need it to accumulate and reset whenever it encounters a different material code. Below is an example:

I need to generate the column "Unidades Acum" and I have tried the following formula:

rangesum(above(sum(Unidades),0,RangeSum(Above(if(only(CodMaterialSAP)=above(total CodMaterialSAP),1,0), 1, RowNo()))))

Do you have any idea? Thanks in advance!

[QFabian]

Hola @charliota , one option is create the accumulation in script, using inter record functions like peek() and previous()

https://help.qlik.com/es-ES/cloud-services/Subsystems/Hub/Content/Sense_Hub/ChartFunctions/InterReco...

Example script :

Data:

Load * INLINE [

Cod Material, Almacen, Fecha Caducidad, Unidades

A, 1, 20/09/2025, 10 

A, 2, 20/09/2025, 50 

A, 3, 22/09/2025, 60 

A, 4, 23/09/2025, 50 

B, 5, 20/09/2025, 10 

B, 6, 23/09/2025, 20 

B, 7, 24/09/2025, 30 

];

Output:

Load

    [Cod Material], 

    Almacen, 

    [Fecha Caducidad], 

    Unidades,

    if([Cod Material] = previous([Cod Material]), Unidades + peek([Unidades Acumuladas]), Unidades) as [Unidades Acumuladas]

Resident Data;

drop table Data;

This is the resulting table

[charliota]

Thanks for the response QFabian, but I need to do this on the Front-End since it has to be dynamic... any other option to do it at the object level instead of the script level?

Thanks in advance!

[QFabian]

@charliota  I almost forgot that we have accumulation as expression propertie!!!!!!

Así queda, como la acumulación aguanta hasta 2 dimensiones, pasé la fecha como expresión :

[charliota]

Thanks for the response, but it doesn’t work for me when I have 3 dimensions in the table. Any other idea?