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

[atcodedog05 · Accepted solution]

Hi @areeba 

In this select tool change fixed decimal 19.2 to 19.2

Hope this helps 🙂

[MarqueeCrew]

@areeba 

Far and away the easiest way to add totals is through the use of the add totals tool inside of the CReW macros! 1 tool wonder

http://www.chaosreignswithin.com/p/macros.html?m=1 

cheers,

mark

[areeba]

Hi @atcodedog05 

I have an addtion in this requirement . I have got another file out of which i need to make similar pivot table with grand total . Like we did in previous solution.

File2:

Employee type        Department       Digit

Fixed                          Plumber             -1

Fixed                          Plumber              -1                  

Fixed                           Cleaning             -3

Fixed                          Colour                  1

Fixed                          white                    1

Contractor               Plumber                -1

Contractor                Plumber           

Contractor                Cleaning              -1

After getting the Pivot out of above data , I need to Combine results of both pivots  as a final output file.

For combining : Employee type : Fixed Plumber will result in -2 , i need to adjust this -2 in final output in a way that this -2 gets deducted from first pivot result which was 2.7(fixed plumber) . So in final output fixed number should be 0.7 and respectively all fields should be filled up.

File1 (final result, which we already had)

Department      Fixed    External    Contractor   Grand total

Plumber             2.7                           1.7                 4.3

File2 (pivot as per new data)

Department       Fixed        Contractor    Grand total

Plumber              -2               -1                     -5

After combining both result of pivot

Department            Fixed        external        Contractor         Grand total 

Plumber                   0.7           (no value)       0.7                       1.4

Main Requirement is to combine both the pivot result and doing calulations and print as a new output file.

Can you give suggestion?

Regards

Areeba

[atcodedog05]

Hi @areeba 

Here is a workflow for the task. I have built from the pivot.

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 

Thank you for the solution, but i am using cross tab in my existing pivot. I am attaching a workflow in which I made both the pivots.

Now, we need to combine them to get the below expected output:

Please note: excel is not allowing me to write -1.0 in Orange department that's why i used brackets to write -1.

Can you suggest now?

Regards

Areeba 

[atcodedog05 · Accepted solution]

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 

Thank you , it really helped.🙂

Regards

Areeba

[atcodedog05]

Happy to help 🙂 @areeba 

Cheers and Happy Analyzing 😀

Feel free to reach out if you face any issues 🙂

[areeba]

Hi @atcodedog05 

From the last pivot we got this.

Is it possible to create a Layout like below screenshot  out of this pivot ? as requirement is to show the data in this layout.

This is only being generated on the basis of fixed and external .

Regards

Areeba

[atcodedog05]

Hi @areeba 

Can you give more info.

Its not very clear how that template is got.