Skip to main content
Announcements
Live today at 11 AM ET. Get your questions about Qlik Connect answered, or just listen in. SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Upali_Wijearatne
Contributor III
Contributor III

Calculation of Opening & Closing Stock

Dear  Experts

I am trying to build a data set for stock opening balances with below Data. I have opening Balance (Bal_BF) of the products and the  Purchases and Sales of the respective months. I want to add Purchases to Bal_BF and deduct the Sales and arrive at Closing Balance (Bal_CF).  Bal_CF of a particular is the Bal_BF of next month and I want to fulfil this in data load script please. Below shown is my data set and expected out put please.Kindly help me.

Upali_Wijearatne_0-1708769595796.png

 

Labels (1)
1 Solution

Accepted Solutions
rubenmarin

Hi, you can use Peek() or Previous() to access previously loaded values, you can do a sorted load by PRD and Date and accumulae values from the previous row, like:

OriginalData:
LOAD 
  Date, 
  PRD, 
  Bal_BF, 
  Purchase, 
  Sales
FROM [DataSource];

Balance:
LOAD
  Date, 
  PRD,
  If(PRD=Peek(PRD)
    ,Peek(Bal_CF)
    ,Bal_BF
  ) as Bal_BF,
  Purchase,
  Sales,
  If(PRD=Peek(PRD)
    ,RangeSum(Peek(Bal_CF),Purchase,-Sales)
    ,RangeSum(Bal_BF,Purchase,-Sales)
  ) as Bal_CF
Resident OriginalData
Order By PRD, Date;

DROP Table OriginalData;

View solution in original post

2 Replies
rubenmarin

Hi, you can use Peek() or Previous() to access previously loaded values, you can do a sorted load by PRD and Date and accumulae values from the previous row, like:

OriginalData:
LOAD 
  Date, 
  PRD, 
  Bal_BF, 
  Purchase, 
  Sales
FROM [DataSource];

Balance:
LOAD
  Date, 
  PRD,
  If(PRD=Peek(PRD)
    ,Peek(Bal_CF)
    ,Bal_BF
  ) as Bal_BF,
  Purchase,
  Sales,
  If(PRD=Peek(PRD)
    ,RangeSum(Peek(Bal_CF),Purchase,-Sales)
    ,RangeSum(Bal_BF,Purchase,-Sales)
  ) as Bal_CF
Resident OriginalData
Order By PRD, Date;

DROP Table OriginalData;
Upali_Wijearatne
Contributor III
Contributor III
Author

Thanks Friend. It works fine