sum all the number and put it in the last second line in the dataset

[Sshasnk]

I have a dataset and I have to sum few fields and put it the row which has total.

Input: 

name	last name	amount	last year amount	debt
Luffy	monkey	1,23,000	10	200
Ageon	targaryen	1200	20,000	150
Leo	Messi	15,000	100	150
Total		139200

Output:

name	last name	amount	last year amount	debt
Luffy	monkey	1,23,000	10	200
Ageon	targaryen	1200	20,000	150
Leo	Messi	15,000	100	150
Total		139200	20200	500

[grazitti_sapna]

Hi @Sshasnk , Please find the workflow below for taking the subtotal  across columns:

[DataNath]

Here's one way I'd go about this @Sshasnk, leveraging the built in 'Total Row' functionality of the Cross-Tab tool:

1) Some of your incoming 'numbers' are currently strings and so the Multi-Field Formula tool addresses this, stripping out commas and changing the data type to numeric.

2) We Transpose the data, bringing the numeric titles into the actual dataset itself, alongside the corresponding values.

3) We immediately re Cross-Tab the data, selecting the built in functionality to generate a total row:

4) We conduct a little bit of clean-up with the Dynamic Rename tool to remove artefacts of the Cross-Tab

5) As we group on 2 fields in the Cross-Tab, we get 'Total' appearing in both and I've therefore just removed this with a standard Formula expression

Hope this helps - any questions or if this isn't quite what you were looking for then please do let us know!

[binuacs]

@Sshasnk One way of doing this

[Sshasnk]

@grazitti_sapna I made one change where I just want to calculate 2 columns only, So basically I have to pick columns which does not have total values in it

[binuacs]

@Sshasnk if you have already a total one column just filter out that and use the transpose and cross tab tool to find the total of all rows