2 Replies Latest reply on Feb 16, 2017 3:34 PM by Lawrence Blasingame

    Need help in creating charts on Hierarchies

    Maheshh C K

      Hi Team,

      I would like to create the consolidated profit for each department & it’s hierarchy structure as well.

       

      S#2 of input has the department details along with its parents. I wanted to see the corresponding department & its sub department profits in a bar graph. Expected output for now is determined in list report for better understanding of the problem & the commets secion.

      Kindly help me on the same on creating it in Tableau.

       

      I have attached the .twbx file for your reference. I just dragged & droped the sum of amount based on department, but i was successful in getting the sum for that particular department only. I was unable to get the sum of all the sub departments under that department.

       

      Input

      S#1: Hierarchy Levels

      Hierarchy Id

      Hierarchy Name

      Parent Hierarchy Id

      1

      Level1

      0

      2

      Level2

      1

      3

      Level3

      2

       

      S#2: Department Details linked to hierarchy (Parent Department Id represents the parent of each organization)

      Department Id

      Department Name

      Parent Department Id

      Hierarchy Id

      1

      College

      0

      1

      2

      ECE

      1

      2

      3

      EEE

      1

      2

      4

      CSC

      1

      2

      5

      IT

      1

      2

      6

      ECE-Theory

      2

      3

      7

      ECE-Lab

      2

      3

      8

      ECE-Operation

      2

      3

      9

      EEE-Theory

      3

      3

      10

      EEE-Lab

      3

      3

      11

      EEE-Operation

      3

      3

      12

      CSC-Theory

      4

      3

      13

      CSC-Lab

      4

      3

      14

      CSC-Operation

      4

      3

      15

      IT-Theory

      5

      3

      16

      IT-Lab

      5

      3

      17

      IT-Operation

      5

      3

       

      S#3: Transaction details including amount & hierarchy

      Transaction Number

      Department Id

      Department Name

      Profit Amount

      1

      1

      College

      10000

      2

      2

      ECE

      10001

      3

      3

      EEE

      10002

      4

      4

      CSC

      10003

      5

      5

      IT

      10004

      6

      6

      ECE-Theory

      10005

      7

      7

      ECE-Lab

      10006

      8

      8

      ECE-Operation

      10007

      9

      9

      EEE-Theory

      10008

      10

      10

      EEE-Lab

      10009

      11

      11

      EEE-Operation

      10010

      12

      12

      CSC-Theory

      10011

      13

      13

      CSC-Lab

      10012

      14

      14

      CSC-Operation

      10013

      15

      15

      IT-Theory

      10014

      16

      16

      IT-Lab

      10015

      17

      17

      IT-Operation

      10016

      18

      2

      ECE

      10017

      19

      3

      EEE

      10018

      20

      4

      CSC

      10019

      21

      5

      IT

      10020

       

      Expected Output: Only gray header is needed in Tableau Orange highlighted header is for understanding on how it is being calculated.

       

      Department Id

      Department Name

      Aggregated Amount including child department

      Aggregation Formula

      Comments

      1

      College

      210210

      10000 + 10001 + 10002 + 10003 + 10004 + 10005 + 10006 + 10007 + 10008 + 10009 + 10010 + 10011 + 10012 + 10013 + 10014 + 10015 + 10016 + 10017 + 10018 + 10019 + 10020

      The profit amount for College + Profit Amount for all the sub departments under college (The sub departments can be traversed with Parent department Id)

      2

      ECE

      50036

      10001 + 10017 + 10005 + 10006 + 10007

      For Row 2 i.e. ECE, The sum of profit amount for ECE department & all its sub departments (i.e. ECE + ECE-Theory + ECE-Lab + ECE-Operations)

      3

      EEE

      50047

      10002 + 10018 + 10008 + 10009 + 10010

      Same as Row#2

      4

      CSC

      50058

      10003 + 10019 + 10011 + 10012 + 10013

      5

      IT

      50058

      10003 + 10019 + 10011 + 10012 + 10013

      6

      ECE-Theory

      10005

      10005

      7

      ECE-Lab

      10006

      10006

      8

      ECE-Operation

      10007

      10007

      9

      EEE-Theory

      10008

      10008

      10

      EEE-Lab

      10009

      10009

      11

      EEE-Operation

      10010

      10010

      12

      CSC-Theory

      10011

      10011

      13

      CSC-Lab

      10012

      10012

      14

      CSC-Operation

      10013

      10013

      15

      IT-Theory

      10014

      10014

      16

      IT-Lab

      10015

      10015

      17

      IT-Operation

      10016

      10016

       

       

      Thanks

      Maheshh