Jump to content


Photo

Reconciling Statistics Report


  • Please log in to reply
5 replies to this topic

#1 mleroux

mleroux

    Advanced Member

  • Members
  • PipPipPip
  • 68 posts

Posted 12 August 2016 - 10:51 AM

I'm trying to reconcile the Statistics list against my database and running into some reconciliation issues and wondering if someone could shed some light on what the data in the report should represent.

In the report there are two lines for marriages, "Individuals with marriage event" and "Married Individuals" which give different results:

Individuals with marriage event

2063 total, 1030 male, 1033 female

Married individuals

3159 total, 1578 male, 1581 female

The Married individuals is simply misnamed – it is couples with some type of partnership, not necessarily marriage. The numbers match the distinct males and distinct females in the familyTable, correctly accounting for same sex partnerships.

So now I’m trying to figure out “Individuals with marriage event”

 

If I look at my data.

I have 1656 families

1237 marriage events. Note: this matches exactly with the “Marriage”

I figured it was easier to start with one gender/sex, so I chose male

of the 1237 marriage events, all of them (1237) have a male (fatherId from familyTable, cross referenced to sex from personTable) associated with them. Since 1237 does not correspond to the 1030 on the statistics report I filtered further

of the 1237 males, several have multiple marriages, so I eliminated these and lowered the count to 1163 distinct males

Then I lower the count by filtering out the marriage events that do not have a date – which I do not believe would be correct, but I am just trying to reconcile the numbers. That left me with 1118 distinct males with a marriage event that is undated.

So no matter how I am looking at this, I cannot come to the number of 1030 males. I may be off on my logic, of course.

Any ideas how the “Individuals with marriage event” is calculated?

 

TIA

marc


Marc
Always learning and loving the discovery process. Focusing on the Huntingdon and Soulanges areas of Quebec - O'Connor/Leroux/Walsh/McCann/Savage/Lalonde/Lauzon


#2 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6256 posts

Posted 13 August 2016 - 09:57 AM

I tried to figure that out and cannot reconcile the RM results with what my logic says they should be either. In my test case, my logic reports 316 distinct males with 1 or more marriage events instead of the report's 184. Either we have both used flawed logic or the developer has! Here is my SQL query based on the label "Individuals with marriage event - Male":

-- males with marriage event
SELECT DISTINCT F.FatherID FROM FamilyTable F
JOIN EventTable E ON F.FamilyID = E.OwnerID AND E.EventType=300
JOIN PersonTable P ON F.FatherID = P.PersonID AND P.Sex=0
;


Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#3 mleroux

mleroux

    Advanced Member

  • Members
  • PipPipPip
  • 68 posts

Posted 13 August 2016 - 04:53 PM

Tom, your query is pretty similar to what I used, so I very much appreciate your duplicating it - at least I'm feeling better that I'm not completely off base

 

Renee, could you see if you can have someone explain what the logic for the report is?

 

Thanks!


Marc
Always learning and loving the discovery process. Focusing on the Huntingdon and Soulanges areas of Quebec - O'Connor/Leroux/Walsh/McCann/Savage/Lalonde/Lauzon


#4 RootsMagician

RootsMagician

    Administrator

  • Admin
  • PipPipPip
  • 826 posts

Posted 15 August 2016 - 11:44 AM

That line is labeled wrong.  It should actually be "Individuals with marriage age" rather than "marriage event" (it should be labeled like the same section for death age).  It is counting those who RM can calculate an age at marriage to provide the min, max, and average age at marriage.  I've fixed this for the next update.


RootsMagician

#5 TomH

TomH

    Advanced Member

  • Members
  • PipPipPip
  • 6256 posts

Posted 15 August 2016 - 03:26 PM

Of course! That's the population on which the age statistics are based. I still have a small discrepancy between the results of my logic and the report: 184 vs 182 respectively.

Revised query:

-- males with marriage age
SELECT DISTINCT F.FatherID FROM FamilyTable F
INNER JOIN EventTable E ON F.FamilyID = E.OwnerID AND E.EventType=300 AND E.Date LIKE 'D%'
INNER JOIN PersonTable P ON F.FatherID = P.PersonID AND P.Sex=0
INNER JOIN EventTable EB ON P.PersonID = EB.OwnerID AND EB.EventType IN (1,3) AND EB.Date LIKE 'D%' 
; 

Tom user of RM7550 FTM2017 Ancestry.ca FamilySearch.org FindMyPast.com
SQLite_Tools_For_Roots_Magic_in_PR_Celti wiki, exploiting the database in special ways >>> RMtrix-tiny.png app, a bundle of RootsMagic utilities.


#6 mleroux

mleroux

    Advanced Member

  • Members
  • PipPipPip
  • 68 posts

Posted 16 August 2016 - 01:25 PM

Agreed, it does make sense - once it's been explained.

 

I am off by a considerable amount if I run Tom's query (1205) and the report (1188) so there is something slightly off. Perhaps some of the conditional dates (after, before, about, between) are excluded. I get a closer number (1198) when I exclude these (changing the date check for birth/marriage to "like 'D.%'). There may be some of the approximate dates (date position 13) that are also excluded, but I can't seem to find a combination that gets me closer. There are a lot of possibilities and no guarantee that I'd be correct  even if I managed to get the numbers to match.


Marc
Always learning and loving the discovery process. Focusing on the Huntingdon and Soulanges areas of Quebec - O'Connor/Leroux/Walsh/McCann/Savage/Lalonde/Lauzon