Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Opening and Closing Balance for trading in the day level

Hi All,

I am trying to generate the Opening and Closing balance for the trading. I am not able to get the correct results in the below method using peek and previous.

tempsales:

LOAD DD_Date as OC_Date,DD_Day as Day

Resident DATE_DIM;

left join(tempsales)

tempsales:

LOAD Date as OC_Date,SUM(IF(S_Total_Amount=0,0,S_Total_Amount)) AS Sales 

Resident Sales

group by Date;

left join(tempsales)

tempsales:

LOAD Date as OC_Date,SUM(IF([P_Total Amount]=0,0,[P_Total Amount])) AS Purchase

Resident Purchase

group by Date;

NEWTABLE:

LOAD OC_Date,Day,ALT(Sales,0) AS Sales,ALT(Purchase,0) AS Purchase ,if(IsNull(Previous(OC_Date)) ,6021121,Peek(Closing_Bal)) AS Opening_Bal,

if(IsNull(Previous(OC_Date)) or OC_Date<>Previous(OC_Date),6021121+Alt(Purchase,0)-Alt(Sales,0),Peek(Closing_Bal)-Alt(Sales,0)+Alt(Purchase,0)) AS Closing_Bal

Resident tempsales order by OC_Date;

drop table tempsales;

In this above method my closing balance is subtracting the previous day sales and purchase as well and also when there is no data for sales and purchase then it is taking the opening balance.

Can anyone please help me on this issue?

Regards,

viswa

3 Replies
vvira1316
Specialist II
Specialist II

Hi,

Are you looking for following

Open_Close_Bal.PNG

NoConcatenate
Opening_Closing_Bal_Tmp:
LOAD *
Inline [OC_Date, Sales,Purchase
7/1/2017, 0, 0
7/2/2017, 0, 0
7/3/2017, 0, 0
7/4/2017, 0, 0
7/5/2017, 13622, 0
7/6/2017, 7.93, 106545.25
7/7/2017, 128934.29, 62323.32
7/8/2017, 112321.23, 0
7/9/2017, 0, 0
7/10/2017, 115441, 174521.21]
;

SET vOpeningBal = 621111;
SET vClosingBal = 0;
SET vRowCount = 0;
LET vRowCount = NoOfRows('Opening_Closing_Bal_Tmp');

FOR i = 0 to $(vRowCount)-1

LET vOC_Date = Peek('OC_Date', $(i), 'Opening_Closing_Bal_Tmp');
LET vSales = Peek('Sales', $(i), 'Opening_Closing_Bal_Tmp');
LET vPurchase = Peek('Purchase', $(i), 'Opening_Closing_Bal_Tmp');

LET vClosingBal=$(vOpeningBal) + Peek('Purchase', $(i), 'Opening_Closing_Bal_Tmp') - Peek('Sales', $(i), 'Opening_Closing_Bal_Tmp');

[Opening_Closing_Bal]:
LOAD *
Inline [OC_Date, Sales, Purchase, Opening_Balance, Closing_Balance
$(vOC_Date), $(vSales), $(vPurchase), $(vOpeningBal), $(vClosingBal)]
;

LET vOpeningBal=$(vClosingBal);

NEXT i;

DROP Table Opening_Closing_Bal_Tmp;

Not applicable
Author

HI Viru,

I am taking the fields sales and purchase from different table and when i try that it is not working.

Opening_Closing_Bal_Tmp:

LOAD DD_Date as OC_Date,DD_Day as Day

Resident DATE_DIM;

left join(Opening_Closing_Bal_Tmp)

Opening_Closing_Bal_Tmp:

LOAD Date as OC_Date,SUM(IF(S_Total_Amount=0,0,S_Total_Amount)) AS Sales 

Resident Sales

group by Date;

left join(Opening_Closing_Bal_Tmp)

Opening_Closing_Bal_Tmp:

LOAD Date as OC_Date,SUM(IF([P_Total Amount]=0,0,[P_Total Amount])) AS Purchase

Resident Purchase

group by Date;

SET vOpeningBal = 621111;

SET vClosingBal = 0;

SET vRowCount = 0;

LET vRowCount = NoOfRows('Opening_Closing_Bal_Tmp');

FOR i = 0 to $(vRowCount)-1

LET vOC_Date = Peek('OC_Date', $(i), 'Opening_Closing_Bal_Tmp');

LET vSales = Peek('Sales', $(i), 'Opening_Closing_Bal_Tmp');

LET vPurchase = Peek('Purchase', $(i), 'Opening_Closing_Bal_Tmp');

LET vClosingBal=$(vOpeningBal) + Peek('Purchase', $(i), 'Opening_Closing_Bal_Tmp') - Peek('Sales', $(i), 'Opening_Closing_Bal_Tmp');

[Opening_Closing_Bal]:

LOAD *

Inline [OC_Date, Sales, Purchase, Opening_Balance, Closing_Balance

$(vOC_Date), $(vSales), $(vPurchase), $(vOpeningBal), $(vClosingBal)];

LET vOpeningBal=$(vClosingBal);

NEXT i;

DROP Table Opening_Closing_Bal_Tmp;

Your screen output is my expected results.

vvira1316
Specialist II
Specialist II

Hi,

Can you please share your sample data file. It will allow me to modify the script accordingly.

Thanks,

Vijay