Accumulated Calculation Problem

[shamitshah]

Hi,

I have an issue with accumulated balances (see below). On 10/11/2015 the accumulated balance should be 504 instead of 1,512.

Same problem on23/11/2015.

I have the following in the script:

ProductBalances:

NoConcatenate

Load ProductID,

RecordDate1,SKH_Quantity1,

If( ProductID=Peek( ProductID ),

RangeSum(SKH_Quantity1,Peek(AccumulatedSKH_Quantity1)),

RangeSum(SKH_Quantity1)) as AccumulatedSKH_Quantity1

Resident TempProductBalances

Order By ProductID, RecordDate1;

Drop Table TempProduct_x_Dates, TempProductBalances;

Appreciate your assistance.

Thanks

Shamit

[Anonymous]

Hi Shamit Shah,

It gives the impression that you have duplicated records in your data with the same ID. Maybe you could verify the records for those specific dates.

Regards,

-- Karla

[sujeetsingh]

First of all I am confused about the accumulation scope. Is it is script or for UI.

For accumulation in UIfollow this link below:

accumulate in straight table | Qlik Community

If it is in script then follow below link

Accumulation in Script

[CELAMBARASAN]

Looks like you have problem with sorting

May be try this

ProductBalances:

NoConcatenate

Load ProductID,

RecordDate1,SKH_Quantity1,

If( ProductID=Peek( ProductID ),

RangeSum(SKH_Quantity1,Peek(AccumulatedSKH_Quantity1)),

RangeSum(SKH_Quantity1)) as AccumulatedSKH_Quantity1

Resident TempProductBalances

Order By RecordDate1, ProductID;

Drop Table TempProduct_x_Dates, TempProductBalances;

[shamitshah]

Hi,

This is the full script (see below).

The idea was to bring in the missing dates and then try and accumulate the numbers. I have used the approach based on ideas on the forum, but I am still having issues.

The reason for using RecordDate1 and SKH_Quantity1 was to avoid having any syn issues.

HISTORY:

LOAD

    Date([SKH_RecordDate] + MakeDate(2000,1,1))as RecordDate,

    "SKH_BaseStockID" as ProductID, 

    "SKH_Quantity";

SQL SELECT *

FROM "SKH_StockHistory";

// ---- A: Load all existing product balances

TempProductBalances:

Load  ProductID,"RecordDate" as RecordDate1, "SKH_Quantity" as SKH_Quantity1,

ProductID & '|' & Num( RecordDate ) as Product_x_DateID

RESIDENT

HISTORY;

MinMaxDate:

Load Min(RecordDate1) as MinDate, Max(RecordDate1) as MaxDate resident TempProductBalances;

Let vMinDate = Peek('MinDate',-1,'MinMaxDate') - 1;

Let vMaxDate = Peek('MaxDate',-1,'MinMaxDate') ;

Drop Table MinMaxDate;

// ---- B: Create all combinations of product and date

TempProduct_x_Dates:

Load distinct ProductID Resident TempProductBalances;

Join (TempProduct_x_Dates)

Load Date(recno()+$(vMinDate)) as RecordDate1 Autogenerate vMaxDate - vMinDate;

// ---- C: Append missing records onto the product balance table

Concatenate (TempProductBalances)

Load * Where not Exists( Product_x_DateID );

Load ProductID, RecordDate1,

ProductID & '|' & Num( RecordDate1 ) as Product_x_DateID

Resident TempProduct_x_Dates ;

// ---- 😧 Create final product balance table. Propagate value from above record.

ProductBalances:

NoConcatenate

Load ProductID,

RecordDate1,SKH_Quantity1,

If( ProductID=Peek( ProductID ),

RangeSum(SKH_Quantity1,Peek(AccumulatedSKH_Quantity1)),

RangeSum(SKH_Quantity1)) as AccumulatedSKH_Quantity1

Resident TempProductBalances

Order By RecordDate1, ProductID; // so that above values can be propagated downwards

// ---- E: Drop all temporary tables

Drop Table TempProduct_x_Dates, TempProductBalances;

Table view:

Thanks

Shamit

[sunny_talwar]

Shamit, I don't know if this will resolve your issue or not, but the order by is not correct. You need to order it by ProductID and then by RecordDate1

ProductBalances:

NoConcatenate

Load ProductID,

RecordDate1,SKH_Quantity1,

If( ProductID=Peek( ProductID ),

RangeSum(SKH_Quantity1,Peek(AccumulatedSKH_Quantity1)),

RangeSum(SKH_Quantity1)) as AccumulatedSKH_Quantity1

Resident TempProductBalances

Order By ProductID, RecordDate1;

[shamitshah]

Hi Sunny,

It works fine on a small data set by using:

Order By Product, RecordDate1

For some reason, it does not work when applied to a large data set.

Thanks

Shamit

[sunny_talwar]

I think you need to check those specific days and look for anomaly in your data. If you check 10/11/2015 the data is triplicating which might hint that something might be triple counted for that day. What do you see when you do Avg(AccumulatedSKH_Quantity1) instead of Sum(AccumulatedSKH_Quantity1)?

also what happens if you rename ProductID to ProductID1? and then create the same table. Is this getting influenced by a link to other tables.

I think you will need to troubleshoot the issue at your end because I am sure you won't be able to share all the data with us