topic Re: Re: Accumulation in script problem in QlikView

Accumulation in script problem

lylererger — Thu, 24 Apr 2014 12:46:43 GMT

Hi.

I can't understand how to do accumulation total. I've some product operations like: income (BYE) and charge(SELL).

And Item detailed by fields [Item No_] and [Variant Code]. [Location Code] it's like Warehouse.[Item No_][Item No_]

I'm trying to script:

IF([Location Code]=Previous([Location Code]) and [Item No_] = Previous ([Item No_]) and [Variant Code]= Previous([Variant Code]),NumSum(Peek('QTY_Accum'),Num(Quantity)),Num(Quantity))

But it's not working (

My task to count a goods remaining balance accumulatively.

Thx.

Re: Accumulation in script problem

hic — Thu, 24 Apr 2014 15:31:46 GMT

In principle, your script looks OK. But I would still make the following changes:

1) Put the "Order By" in the same Load as where you define your QTY_Accum.

2) Define your QTY_Accum the following way:

Num(If( [Posting Date] = Peek([Posting Date]) and

[Location Code] = Peek([Location Code]) and

[Item No_] = Peek([Item No_]) and

[Variant Code] = Peek([Variant Code]),

RangeSum(Peek('QTY_Accum'), Quantity),

Quantity )) as QTY_Accum

3) Add a debugging field to see the true record order withing the Load:

RecNo() as RecordNumber

Good luck

HIC

Re: Re: Accumulation in script problem

lylererger — Thu, 24 Apr 2014 18:39:11 GMT

Yes HIC.
But we shouldn't consider the posting date.
Some peace of code:

[tmp]:
LOAD * INLINE [
  'Posting Date','Location Code', 'Item No_', 'Variant Code', 'Entry Type',Quantity
    01.01.2013, 1, 1, 1, BYE, 10
    02.01.2013, 1, 1, 1, BYE, 5
    02.01.2013, 1, 1, 1, SELL, -3
    03.01.2013, 2, 1, 1, BYE, 20
    03.01.2013, 2, 1, 1, SELL, -10
];


tmp2:
NoConcatenate
LOAD
  RecNo() as Rec,
  [Posting Date],
  [Location Code],
  [Item No_],
  [Variant Code],
  [Entry Type],
  Quantity,
  Num(If([Posting Date]  = Peek([Posting Date])  and [Location Code] = Peek([Location Code]) and  [Item No_] = Peek([Item No_]) and [Variant Code]  = Peek([Variant Code]), RangeSum(Peek('QTY_Accum'), Quantity), Quantity ))) as QTY_Accum,
  Num(IF([Location Code] = Peek([Location Code]) and  [Item No_] = Peek([Item No_]) and [Variant Code]  = Peek([Variant Code]),NumSum(Peek('QTY_Accum2'),Num(Quantity)),Num(Quantity))) as QTY_Accum2
Resident tmp
Order by [Posting Date] asc,[Location Code] asc,[Item No_] asc,[Variant Code] asc;
Drop Table tmp;

Looks like we shouldn't consider date in condition of accumulation.

Any way thnx a lot.

eek('QTY_Aeek('QTY_A

Re: Accumulation in script problem

thkarner — Tue, 07 Jun 2016 17:31:41 GMT

Try this (to be modified for your situation)

// assumption: data is on level of %CATEG1_KEY, %CATEG2_KEY, Year, Month
LOAD
  *,
  If(Year = Peek('Year', -1) AND // if year of current record = year of pevious record and
    %CATEG1_KEY = Peek('%CATEG1_KEY', -1) AND // if same %CATEG1_KEY as previous record and
      %CATEG2_KEY = Peek('%CATEG2_KEY', -1), // if same %CATEG2_KEY as previous record
  RangeSum(Peek('Sales_Acc', -1), Sales), // previous month + current month
  RangeSum(Sales)) // use only first month of new year
  AS Sales_Acc
Resident Sales
ORDER BY %CATEG1_KEY, %CATEG2_KEY, Year, Month; // order records by all categories (%CATEG1_KEY, %CATEG1_KEY), Year and Month to use peek function for accumulation

If this helps you please mark it as helpful or correct.

Thomas