Totalling horizontally and vertically

[areeba]

Hi ,

I Have 3 Columns :

For eg:

1)Employee type

2)Digit ( it is in  v_wstring  because it had prior calculations and conversions.)

3)Department

I want to make a table in which i want to calculate the total(horizontally and vertically) on the basis of department  who is associated to  Employee type and digit.

Please find attached Input file and expected output file for more clarification

Can you help?

Thanks 

Areeba

[Per]

Hi @areeba 

For changing the structure of the data to fit your purpose, you need to use the Cross Tab tool which will get you what you need, minus the grand totals.

I do not think there is a nice way to get the grand totals formatted as you asked for, but please see the attached workflow for one suggestion how it can be done

[atcodedog05]

Hi @areeba 

Here is a workflow for the task.

Output:

Workflow:

Hope this helps 🙂 Feel to ask if you have any questions

If this post helps you please mark it as solution. And give a like if you dont mind 😀👍

[areeba]

Hi @atcodedog05 

This looks good but i don't want Null values instead it should be blank.

Second thing is if it single digit anywhere for eg 1 then it should be 1.00 or 3 then 3.00.

In my Input file also its 1.00 , I want to take it as it is because this is the format which client wants to see.

[atcodedog05 · Accepted solution]

Hi @areeba 

Here is the updated workflow

Output

Check and let me know.

[areeba]

Hi @atcodedog05 

Perfect! 

You are awesome!

[atcodedog05]

Happy to help 🙂 @areeba 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[DavidP]

Just one more thing to mention here.

When using a CrossTab tool, you have the option to add column and row totals (scroll down in Method for Aggregating values)

[atcodedog05]

Interesting Takeaway @DavidP 🙂

Thank you for the great new exposure 😀

[areeba]

Hi @atcodedog05 

Just one small thing, in my output here i want to show  it is as below :

For eg: 

1.00----- 1.0

79.13------79.1

13.89------13.9

53.78-------53.8

Can you Suggest?

Thanks

Areeba