Math help

[rivet]

We have a voting DB. After the first round of votes we need some math to find out if there were any ties and build next election accordingly.

• There are several groups 
• each group has a max number of positions available.
• duplicate position numbers mean candidates received the same number of votes.
• Group A,B,C have one tie in each, so the next election the will be two candidates per group with a max position of 1.
• Group D, will have a four candidates with a max position of 2

Trying to wrap my mind around this let me know if you have any thoughts.

        
GROUP A - Max positions 13	
cand.	pos.	tie
01	1	
02	2	
03	3	
04	4	
05	5	
06	6	
07	7	
08	8	
09	9	
10	10	
11	11	
12	12	
13	13	1
14	13	1
        
        
GROUP B - Max positions 3	
cand.	pos.	tie
01	1	
02	2	
03	3	1
04	3	1
05	
06	
        
GROUP C - Max positions 6	
cand.	pos.	tie
01	1	
02	2	
03	3	
04	4	
05	4	
06	5	1
07	5	1
08	6	bumped
09	
10	
        
GROUP D - Max positions 4	
cand.	pos.	tie
01	1	
02	2	
03	3	1
04	3	1
05	3	1
06	3	1
07	4	bumped
08	
09	
10	
11	

 

Edited July 23, 2015 by rivet

[comment]

I see a scripting challenge here, not a math problem.

I think this would be my starting point: if the number of candidates whose rank¹ is less than or equal to the number of available positions exceeds the number of available positions, then you need to hold a second round.

---

(1) I am using the word "rank" here instead of your "position", to distinguish it from your other use of the same word.

[rivet]

Thanks 'rank' is a better word and yes perhaps a script, I can see it looping through the ranks low to high to see when the count of candidate in a rank range is equal or greater then the max position.  ( if that is what you are getting at. )

[comment]

More or less. I believe I would do this by looping on groups by rank. Start with the candidates with rank 1; if their count allows it, award them the positions, and move to the next group (after subtracting the awarded positions). If the count is too high, then this group needs to be entered into the next round.

[LaRetta]

Great learning thread ... thanks, guys. :-)

[rivet]

Almost there, I believe.  I figured I am only concerned if any nominees vote count matches that of the the last (maxVotes) nominees vote count.  In this example there are four positions available and the fourth nominee from the top of the 'arrayFull' has a vote count of 18.  I then search for vote counts of 18 in the full array and extract the winners and ties from that result.

I could script or even I can turn into a CF and pull the variables I need for that particular field.  Regardless I am now stuck. The winner and tie result will be stored as an array in their respect fields but I only need the ID (key) on the right.   So I need an auto calc to strip "¶*|"  

( if you see my blind spots please let me know )

 

Let ( [

votemax = election_electoralgroup::votemax ;

//__________________________ PULL FULL ARRAY
arrayFull = 
ExecuteSQL ( "
SELECT
count ( id_nominee ) AS rank, id_nominee
FROM
vote
WHERE
id_election = ? AND
id_electoralgroup = ?
GROUP BY
id_nominee
ORDER BY
rank DESC

" ; "|" ; "¶"; election_electoralgroup::id_election ; election_electoralgroup::id_electoralgroup ) ;

arrayFull_count = ValueCount ( arrayFull ) ;



//__________________________ GET LAST ROW and TARGET VOTE COUNT

lastRow = GetValue ( arrayFull ; votemax ) ;
burst = Substitute ( lastRow ; "|" ; "¶" ) ;
vote = GetValue ( burst ; 1 ) ;



//__________________________ FIND MULTIPLE INSTANCES OF VOTE

q = vote & "|" ;
match_count = PatternCount ( arrayFull ; q )  ;
tie = match_count > 1 ;

firstInstance = Position ( arrayFull ; q ; 1 ; 1 )  ;
arrayWinner = Left ( arrayFull ; firstInstance - 1 ) ;
arrayWinner_count = ValueCount ( arrayWinner ) ;

arrayTail = Substitute ( arrayFull ; arrayWinner ; "" ) ;
arrayTie = LeftWords ( arrayTail ; 2 * match_count  ) ; 
arrayTie_count = ValueCount ( arrayTie ) ;

remainingPositions = votemax - arrayWinner_count


] ;

""

)

full     	winner   	tail    	tie
36|43511	36|43511	18|45037	18|45037
34|43271	34|43271	18|45050	18|45050
18|45037        		18|45033	18|45033
18|45050        		18|45005	18|45005
18|45033        		16|44972	
18|45005        		11|45030	
16|44972        		9|44787	
11|45030        		9|45021	
9|44787	                	5|45049	
9|45021			
5|45049			

 

 

Edited July 25, 2015 by rivet

[comment]

I was thinking of doing this in native Filemaker. Something like the attached sketch.

FastSummarizeVotes.fp7

[rivet]

That works well and its snappy.  I will take a deeper look to see how it would work with my schema.  Thanks again