Calculate opening and Closing balance

[rohan_mulay]

Hii All,

I have a requirement where i need to find opening and closing balance for ID's as given in the attached excel file.

In the attached excel, I have purchase and sales data for two ID's 'A' and 'B'. This data has a Date field having date from Jan Feb and Mar months only. The expected result for Opening and closing balances result are calculated in excel for ID's A and B jus so that we can cross check the QV output and the excel output. This expected output is highlighted in green color in the attached excel file.

-In the given data, the first record says  ID 'A'  has done the purchase of 10000 on 2nd Jan 2014 and no Sales. Thus the opening Balance for 'A' is zero '0' since there is no Sales or Purchase for 'A' before 2nd Jan2014. Where as closing for 'A' is 10000 for 2nd Jan2014.

- Next transaction(Purchase) by 'A' is done on 5th Jan 2014. Now here the opening for 5th jan 2014 will be the closing balance of its prev transaction that is purchase done on 2nd jan 2014 Plus the Purchase of 5th jan2014. In short closing of 1st record of 'A' will be the opening of 2nd record of 'A' plus (Purchase-sales) and so on

Note: The data highlighed in green in the attached excel is not pulled in the QV app as it is just the expected result for opening and closing balances. This is ment just for crosschecking with QV output.

[rohan_mulay]

Please find the attachments for my doubts.

[#3]

Hi,

Use This script

Tab1:

LOAD ID,

     Date(Date) as Date,

     Purchase,

     Sales,

     opening,

     Closing

FROM

D:\Vivek\Community\Opening_Closing_Status.xlsx

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Col, Pos(Top, 5))

));

Load ID,

     Date,

     Purchase,

     Sales,

     if(isNull(Previous(ID)) or ID<>Previous(ID),Sales,Peek(Closing_bal)) as  Opening_bal,

     if(isNull(Previous(ID)) or ID<>Previous(ID),Purchase-Sales,Peek(Closing_bal)-Sales+Purchase) as Closing_bal

     Resident Tab1;

Drop table Tab1;

Result will be like this,

I have also attached the QVW file

Regards,

Vivek

[rohan_mulay]

Hii Vivek ,

Thanks for your Logic. Its working as expected but for unsorted data its given wrong values. Please find attached the unsorted data.

[rohan_mulay · Accepted solution]

Heyyy Vivek,

On modifying your code a bit I got the expected result for unsorted data as well.. (I jus added Order By ID,Date )

Many Thanks..

Tab1:
LOAD ID,
     Date(Date) as Date,
     Purchase,
     Sales
FROM

(ooxml, embedded labels, table is Sheet1);

Tab2:
Load ID,
     Date,
     Purchase,
     Sales,
     if(isNull(Previous(ID)) or ID<>Previous(ID),0,Peek(Closing_bal)) as  Opening_bal,
     if(isNull(Previous(ID)) or ID<>Previous(ID),Purchase-Sales,Peek(Closing_bal)-Sales+Purchase) as Closing_bal
     Resident Tab1 order by ID,Date;
Drop table Tab1;

[#6]

Hi,

yeah for unsorted data you have to sort it

so u have to give Order By ID, Date

Regards,

Vivek

[#7]

Hi Vivek,

I am not getting the expected result for my situation as i am not getting the source as 0 when there is no Purchase and sales

on the Dates