topic Accumulation on scripting in QlikView

Accumulation on scripting

Fri, 18 May 2012 09:42:40 GMT

Hi ALL,

Please help me regarding the following requirement which I want to achieve through script

Please find the attched file for the dummy data.

When I click on Year 2011 and Month Jan ... I want the data for Jan 2011

when I select Month Feb... I want data for Jan & Feb... like wise for Mar.. I want data for jan, feb & Mar.. and so on.

Same I want for the year 2012 also.

Re: Accumulation on scripting

thkarner — Tue, 07 Jun 2016 17:35:30 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

Re: Accumulation on scripting

spividori — Tue, 07 Jun 2016 21:23:31 GMT

Hi.

See the example attaching here.

Regards.