topic Re: Accumulated Calculation Problem in QlikView

Accumulated Calculation Problem

shamitshah — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Accumulated Calculation Problem

Anonymous — Fri, 27 May 2016 03:00:34 GMT

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

Re: Accumulated Calculation Problem

sujeetsingh — Fri, 27 May 2016 03:45:43 GMT

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

Re: Accumulated Calculation Problem

CELAMBARASAN — Fri, 27 May 2016 04:20:26 GMT

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;

Re: Accumulated Calculation Problem

shamitshah — Fri, 27 May 2016 06:15:33 GMT

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

Re: Accumulated Calculation Problem

sunny_talwar — Fri, 27 May 2016 09:48:30 GMT

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;

Re: Accumulated Calculation Problem

shamitshah — Fri, 27 May 2016 13:14:24 GMT

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

Re: Accumulated Calculation Problem

sunny_talwar — Fri, 27 May 2016 13:21:10 GMT

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