Family Historian User Group

I was going to do a study on movements of occupations over the census periods.
I enter my occupations from add fact..occupations.
I enter the occupation exactly as per the relative census report and only alter if there is a spelling error.
The date I enter is the date of the census ie 5 April 1891

What I want is either a query or a report that will give me the following :-
1. Per each Census Year
2. Occupation title.

so the report would look this this :-

Occupation List

Occupation description 1841 1851 1861 1871 1881 1891 1901 1911
So the quantity is shown against year for a particular occupation.

This will enable me to see a trend of occupations from my large files.

Can anyone assist please??
I have tried the 'Publish' special Data report Occupation
I have also tried the 'View' Custom query
These are good but dont deliver my request
I am useless at writing such a query myself I have tried and failed.
At M Tates request this has been transferred from the General Forum
Thanks



[confused]

ID:6805

I don't think it is possible to write a Query for that, so I have written a prototype Plugin that should get close to what you want.

Download it via Knowledge Base > FH Plugins > Work In Progress Plugins or directly from Occupations Per Census Version 1.0. ( EDIT - Now in Plugin Store )

Thanks Mike
It would have been nice to have it in Alpha Order and totalled per year column.
I can view but not print off .
I have totalled all occupations per each census but it does not tally with the Occupation list total from 'Special data reports..Occupations.
Is there a doubling up ??
Some of the occupations do not appear to be shown in your WIP.
I would like to print it off and also to send for modelling to Excel
Anyway a lot better than my zero result
Thanks
any further help on this if you have time I would be grateful!!

I have added the following to Version 1.1 in the WiP store.

Plugin Display:
Non-scrolling top row of Absolute Total of all the Occupations.
New first column Total for each row.
New last column Other for Occupations with non-Census years.
That should account for all the discrepancies you have noticed.
(Sorting the Occupations is a little trickier to achieve, but see below.)

Export Spreadsheet:
Entire matrix is automatically exported to a CSV file in your Public folder.
This should open in any spreadsheet program such as Excel.
Sort A-Z on the first column, excluding header row, and Occupations will be in order.
Place cursor in B3 and use View > Freeze Panes to stop first column and top two rows from scrolling.
Expand width of first column, and save as an Excel Workbook to preserve.
You can also print the spreadsheet.

If Excel is available, it would be possible to use a simple Fact query with two columns:

and two row filters:

Then Save Result Set As.. a CSV file, open that in Excel and insert a PivotTable which, by default, would sort the rows, and provide row and column totals.

jsphillips comments:

I have totalled all occupations per each census but it does not tally with the Occupation list total from 'Special data reports..Occupations.
Is there a doubling up ??
Some of the occupations do not appear to be shown in your WIP.

This is because not every Occupation is necessarily in a Census Year, so I have added an Other column to capture all those.

Peter, because of the above, it means that your solution may contain many unwanted YEAR values.
Also Exclude unless %FACT.SOUR>_TYPE% matches 'Census' only works if there are Citations.
I have never used a PivotTable and I presume it collapses multiple rows for the same Occupation into a single row with counts.
I tried using a PivotTable and could get the row & column headings, but not any counts or totals. Can you give some clues.

I'm using Excel 2010, which I find much harder to navigate than Excel 2003.  My first attempt at a 2010 PivotTable looks like this (shrunk to avoid posting a too-large image):

I haven't bothered to tidy up the headings.  You drag the fields to the relevant area in the PivotTable Field List pane.  The Values area drop-down menu allows you to select the appropriate Count.  The drop-down pane  for Year in the Field List allows de-selection of any unwanted years.  I hope this helps.

Thank you Peter, it works for me now, with the following Column change for Year to filter Census Years and capture the rest under 'Other':

=TextIf((%FACT.DATE:YEAR% = 1841) or (%FACT.DATE:YEAR% = 1851) or (%FACT.DATE:YEAR% = 1861) or (%FACT.DATE:YEAR% = 1871) or (%FACT.DATE:YEAR% = 1881) or (%FACT.DATE:YEAR% = 1891) or (%FACT.DATE:YEAR% = 1901) or (%FACT.DATE:YEAR% = 1911),%FACT.DATE:YEAR%,'Other')

and removal of the Row filter Citation check.

Could be more precise about it being a Census Occupation by checking (%FACT.DATE% = DateAt(6,6,1841)) etc for other Census dates.

Thanks Mike & Peter.
I have run Occupations v 1.1 produced the csv and sorted using the 'Auto' column filter.
Great
Still a difference between the Occupation List and this!! To get to the bottom of the difference I have printed off both reports and manually checking off against one another.
Is there any way of discovering for example thos who were an 'Accountant'. 'Occupations List' delivers 2 the csv/Excel delivers 1.
So I need a search to find names against Occupation input.
Any ideas. ??
Once again thanks for a great job !!

Sorry the look up per name per occupation then might reveal a spurious date !!

Why also does the 'blue' 'new' dissapear from some posts and not others when they are ongoing ?? Ie in this post
Thanks

The New! appears when a new reply is posted, and vanishes when you have read it.

There are two ways to analyse your Occupation Facts.

(1) All Facts Query
Use View > Standard Queries > All Facts.
Click on the Fact heading to sort into order.
The Accountant Facts should now be listed together near the top.
If they have a Fact date or Place then double-click it to display the Accountant Fact highlighted in a Property Box.
Otherwise, double-click the Fact Owner and open their Facts tab to find Accountant Fact.

(2) Work with Data
Use Tools > Work with Data > Occupations.
Select Accountant Occupation and click Where Used to obtain a double-clickable Result Set.
Alternatively, select Accountant Occupation and click Records button.
Then either click on + to expand data tree, or use Go To button.

I am working on a variant of the Plugin that simply creates a Result Set of the statistics, which can then be Sorted in various ways, easily Printed, and exported in various formats including CSV.
For general use, I hope it will detect which Census Events are in the GEDCOM so it automatically adapts the Census Years, then I can publish it in the Plugin Store.

Mike,
This plugin only captures the data from UK census, (And probably Canada), but What about US Census years. Or would these appear under 'Other'?

In Version 1.1 they would appear under Other but I hope the next version will auto-adapt to your GEDCOM Census Events.

Occupations Per Census Version 1.2 is available. ( EDIT - Now in Plugin Store )

This version automatically determines the Census Years from your GEDCOM Census Events regardless of whether they are UK, or USA, or any other, or any combination thereof.
It does not at present consider Census (family) Events.

It immediately creates a Result Set of the Occupations Per Census sorted by Occupation.
This can be Printed, or Saved As a PDF or CSV or TXT file.
The CSV file can be opened in a spreadsheet such as Excel.
The Occupation is considered to belong to a Census if the Date Year matches, but Day & Month may differ.
Any Occupation not belonging to a Census Year is logged under the Others column.
Furthermore, the Result Set columns can be sorted in Ascending or Descending order as desired.
e.g.
Alt+click on Occupations Per Census column will reverse sort the Occupations.
Alt+click on the Totals column will sort in order of Occupation popularity.

WELL DONE
Thanks Just what was needed
The only query I have is as follows:-
Underneath the headings and the underlining and before the total line appears '2' in the total column & '2' in the others column what is this please ??

It now agrees to the total produced to the Occupation List.

here is a cutout of the query '2'

Aha..
I had one like this.
It is an occupation fact that is blank (I had put the occupation in the place field by mistake.)

If you run the standard all facts query, then run down to occupations then you can find it.

I actually created a copy of the query restricted to Occupation facts.

Ah! That is caused by an Occupation with no Occupation: field Value entered.
Use View > Standard Queries > All Facts, click on Fact column heading, scroll down to Occupation, and look for an entry with a blank Value.

I will adjust that in next Version to say ~ Occupation value is blank ~, but should this appear immediately under Totals, or right at the bottom of the list of Occupations???

I am glad the statistics now all agree.
There was a small bug I discovered that probably accounted for the errors.

Fine Thanks
Surely this brings up the fact that a query is required to find any fact that has no date or info written to it

OK now found the 2 rogue entries and deleted.
Thanks again.

Yes!
Just make a copy of the All Facts Standard Query.
Then add Rows tab exclusion Filters for the blank field criteria:
e.g.
Exclude unless %FACT.DATE% is null
Exclude unless %FACT.PLAC% is null
Exclude unless %FACT.ADDR% is null
Exclude unless =FactValue() is null
Exclude unless =FactLabel() matches 'Occupation'
Exclude unless =IsAttribute() is true
Exclude unless =IsEvent() is true


Should ~ Occupation value is blank ~ appear immediately under Totals, or right at the bottom of the list of Occupations???
Your preference would be welcome.

Thanks a lot.
I am happy with the 1.2 version as it is.
Please leave in this state for my research.
But an additional query for the blank fields (not an adjusted query above) would be welcome with the reult ABOVE the totals
Thanks

Also could this query handle other mising factors but substituting 'occupations' wirth Births etc ??
By the way I meant BELOW totals
Thanks