Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Highlighted
Not applicable

Accumulate the Value in Script

Untitled.jpg

can you help me, ? i want to accumulate the value in the script, but i don't know how to make it, i tried to use range sum and  peek() but it doesn't work, help me please

2 Replies
MVP & Luminary
MVP & Luminary

Re: Accumulate the Value in Script

Perhaps like this:

Temp:

LOAD ItemCode,

     QtyPackingslip,

     Date,

     Product

FROM [comm182616.qvd] (qvd);

Result:

LOAD * , if(ItemCode = previous(ItemCode), rangesum(QtyPackingslip,peek([Acc Qty Packingslip])), QtyPackingslip) as [Acc Qty Packingslip]

RESIDENT Temp

ORDER BY Product, ItemCode, Date;

DROP TABLE Temp;


talk is cheap, supply exceeds demand
Partner
Partner

Re: Accumulate the Value in Script

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