I started a forum a few months back and got some really good lead from a Data Monarch Jim (Data source joining, matching the rows and hierarchies ), however, it didn't get me to the "final product" I am trying to build. Frankly, I had to put it on the shelf for a while, for which I apologize to Jim, but now I am ready to crack this nut.
I have some occupation data from multiple counties down to zip code and I need to build a hierarchy allowing me to expand and collapse and total the job numbers or resident workers at different levels of hierarchy. Each occupation and each occupation group is associated with a specific unique SOC code (Key for that is in the attached Excel sheet). Jim led me towards some LODs in the previous post and I think I am on the right track (he built 4 sheets - label hierarchy, label hierarchy (2), label hierarchy (3) and JD check. But I also wonder if my Data join is done so that I can get what I need. The problem is I am showing Occupation descriptions only at the lowest levels and also, as I back out, my math is not right.
Jim dissected each 6 digit SOC code (the most granular one) into three pieces to create the labels and then it looks like he worked back and built major minor and broad categories SOC codes for me. It helped great deal.
In the attached image and in the attached workbook, I have the hierarchy drilled all the way down. (Jana 9/7 worksheet in the attached workbook)
But if I start collapsing it back, the numbers at the broad, minor and major group remain the same. I am looking for those numbers to be summed up (thus grow) for each respective group as you hit the minus sign (and vice versa - numbers break down and are distributed among respective occupations as you hit the plus sign). With drilling and collapsing the hierarchy, I am also looking to be able to view the Occupation descriptions for major, minor and broad groups. Right now I am only able to see the detailed description. Those should be pulled from the Excel file that I attached and I have the data joined (hopefully correctly). I feel like the glitch may be somewhere there in my joining but I am unsure.
Just to make sure I am expressing myself correctly, if you follow the Excel sheet I attached,
I want to be able to show:
11-0000 Management Occupations 500 Jobs (the number is made up)
then as you hit the minus sign, the 500 jobs should break down to:
11-1000 Top Executives 100 jobs
11-2000 Advertising, Marketing, Promotions, Public Relations, and Sales Managers 100 jobs
11-3000 Operations Specialties Managers 100 jobs
11-9000 Other Management Occupations 200 jobs
and so on all the way down to detailed occupation 6-digit SOC code. Makes sense?
I will be digging further, as I feel like I am close, but any suggestion anyone may have would be great.