Derive monthly closing balance of various items from yearly opening balance and their monthly transactions data

Scenario:

The dataset has yearly opening balance and their monthly transactions for different items. 

Requirement: 

Need to derive monthly closing balance of various items and ledgers.

Step 1:

To derive the closing balance of the first month say; April, you need to create a new custom column by summing up the opening balance.

Below is the AddColumn panel through which we can add a custom column through custom expression to derive the Closing balance of every month.

 

For getting closing amount of April month we have summed up opening balance and monthly transaction of April.

Below is the expression which is used to fulfil our requirement of getting closing balance of every month.

 

Similarly, for every month sum up the closing balance of previous month and transaction of that particular month.

Step 2:

Next, for getting the Opening Balance for every month we need to copy closing balance column of previous month and rename it as opening balance column for current month.

For example: - Closing Balance of April will be considered as Opening Balance for May. 

Step 3:

To merge the opening balance and closing balance, you need to do some transformations as below.

As a first step,

Change the data type of all the opening balance and closing balance from integer type to string type.

We are converting the data type to string so that we can do the transformation operation like concatenation which we are doing in next step.

Step 4:

Now concatenate the balance of every month with their respective month name.

We are concatenating the opening and closing balance with their respective month name so that we can differentiate between the balance amounts of different months.

For this right click on opening/closing balance column -> Transform -> more -> select “concat” operation.

Below is the resultant dataset after concatenating the opening balance and closing balance with their respective months.

Also you can delete the unwanted columns.

Step 5:

Now merge the opening balance and closing of each month separately with the help of a separator (like ‘:’).

We are merging these columns so that we can get opening and closing balance of each month in one column.

For Example:-

 

Merge_Apr	Merge_May	Merge_Jun
4000Apr:3000Apr	4500May:5000May	6000Jun:5500Jun
3000Apr:4500Apr	3400May:3900May	4000Jun:4300Jun

Syntax used: - Opening Balance:Closing Balance

Below is the dataset after merging the opening balance and closing balance of all the months.

Step 6:

Now merge all the above merged columns into a single column with a different separator (like ‘;’).

We are merging again so that we can get the opening balance and closing balance of all months into a single column.

For Example:-

Before merging all merged columns

 

Merge_Apr	Merge_May	Merge_Jun
4000Apr:3000Apr	4500May:5000May	6000Jun:5500Jun
3000Apr:4500Apr	3400May:3900May	4000Jun:4300Jun

 

After merging all Merged Columns

Month_Bal_Merge

4000Apr:3000Apr; 4500May:5000May; 6000Jun:55000Jun

3000Apr:4500Apr; 3400May:3900May; 4000Jun:4300Jun

Step 7:

Now split the column as per the separator to pivot the opening balance and closing balance into column.

We need to split by the semi colon (;) first and then by the colon (:).

For Example:-

Before Splitting	After Splitting
4000Apr:3000Apr;4500May:5000May;6000June:5500June	4000Apr:3000Apr
	4500May:5000May
	6000June:5500June

 

To achieve this right click on all merged column -> Split -> Spilt to row (separator ‘;’).

Below is the result dataset that we will get after splitting.

Now split column by the colon (:).

To achieve this right click on all merged column -> Split -> Spilt to column (separator ‘:’).

For Example:-

Opening_Balance	Closing_Balance
4000Apr	3000Apr
4500May	5000May
6000Jun	5500Jun

 

Below is the resultant dataset that we will get after splitting the column.

Step 8:

We need to split the column of opening balance and closing balance to dissociate the month name into separate column.

We are splitting month name from the opening and closing balance amount so that we can get a separate column of month name in front of its opening and closing balance.

To achieve this right click on all merged column -> Split -> Spilt to column (length 3 from right).

 

After splitting months from both columns you will get two month columns, so delete anyone of it and keep single month column to get the required dataset.

For Example:-

Month	Opening_Balance	Closing_Balance
Apr	4000	3000
May	4500	5000
Jun	6000	5500

 

Below is the final dataset where have achieved the result required as of month and opening/closing balance is single column each.

Note: This article is based on Smarten Version 5.x. This may or may not be relevant to the Smarten version you may be using.

Closing balance Dataset Smarten SSDP Monthly closing balance Yearly Opening balance Opening balance