Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
On May 18th at 10AM EDT we will answer your QlikView questions live. REGISTER
cancel
Showing results for 
Search instead for 
Did you mean: 
shamitshah
Partner
Partner

Accumulated Calculation Problem

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

7 Replies
karmariv82
Creator III
Creator III

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
Master III
Master III

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
Partner
Partner
Author

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
Partner
Partner
Author

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