Cummulative in the script

[#1]

Dear all,

I´m facing some problems when I try to make an agregation of the stock quantity comning from an historic file.

I´ve tryed many kind of expressions in order to reach the accumulation of this value (Cantidad) .

I do not know if the Rangesum (peek) expression are not working because the date were not ordered , but I tryed to order them too but without result.

Can someone help me? What am I doing wrong? Here you have some of the expressions I tryed and the qvw file .

Rangesum(Peek(Cantidad))as AcumuluadoYAYA2,

RangeSum(Cantidad, Peek('Cantidadacumulada')) as Cantidadacumulada,

if(Previous(IdTienda)=IdTienda, rangesum(Cantidad,peek(Cumulative)),Cantidad) as Cumulative,

numsum(Cantidad, peek([Cumm Cantidad])) as [Cumm Cantidad],

(Cantidad + If(Not IsNull(peek(Cantidad)),peek(Cantidad),0)) as [Cummulative Cantidad],

peek(Cantidad) as CantAnterior,

if(peek(IdTienda)=IdTienda,peek(Cumm)+Cantidad,Cantidad) as Cumm1,

The target is to get a cummulative value of field "cantidad" which it suits to the desired filters (IdTienda or IdArticulo).

Thanks in advance and regards.

Asier

[swuehl]

If you are asking for help with scripting, it would make things easier when you post either the input files, too, or create a script with all data needed in INLINE tables or using AUTOGENERATE etc. So that we can reload the script after modifications.

I am using a BINARY LOAD to load your existing table, then I tried to create the accumulated field like

CUMMULATIVE:

NOCONCATENATE

LOAD *,

  if(previous(IdTienda) = IdTienda and previous(IdArticulo) = IdArticulo, rangesum(Cantidad,peek(Accum)),Cantidad) as Accum

RESIDENT "ORIGINAL-1"

ORDER BY IdTienda, IdArticulo, Fecha;

Since it seems you want a cummulation per IdTienda and per IdArticulo, both fields should be used to sort your data (and also Fecha to get the stock over time correctly.

If you want to accumulate per IdTienda and per IdArticulo, you also need to check both fields in the calculated field expression for change, then either accumulate using rangesum() or reset with the current Cantidad.

[#3]

Thanks for your support swuehl,

the example from your .qvw as it´s loaded it´s still presenting errors.

I´ve added the excel file,that maybe could help.

See the image attached.

Thanks again.

[swuehl · Accepted solution]

I think its working fine, it's just that these records show the same IdTienda, IdArticulo and Fecha.

The table box does not use the load order, but Accum Field to sort these records.

You can create an additional LoadOrder field:

CUMMULATIVE:

NOCONCATENATE

LOAD *, recno() as LoadOrder,

  if(previous(IdTienda) = IdTienda and previous(IdArticulo) = IdArticulo, rangesum(Cantidad,peek(Accum)),Cantidad) as Accum

RESIDENT "ORIGINAL-1"

ORDER BY IdTienda, IdArticulo, Fecha;

and sort your table box by LoadOrder, this should demonstrate that the script works as intended.

[#5]

now It´s working perfectly!

Thanks