Scoring Database

[Neil Scrivener]

Hi Guys, 

I am building a Scoring Database,  which was originally in Excel. 

The way I build this in Excel was as follows:

	Metric 1 Multiplier	Metric 2 Multiplier	Metric 3 Multiplier	Metric 4 Multiplier	Metric 5 Multiplier		SCORE
	10	8	6	3	6
Item & Score
Apple	3*10	4*8	8*6	8*3	9*6		178
Bananna

 

So, for example the Apple has a score of 3, 4, 8, 8 and 9 respectively. It is then times by the appropriate metric multiplier. 

The score at the end is relevant to the rest result. 

The data input of Item and Score is fine. 

What I have done so far is made a table for the Metric Multiplier 1, 2, 3, etc, with only 1 record in, called 'Metrics Table'. 

So I have two questions; 

1. What is the best way to create the Score, as above? Would it be to make a new table, called "Results" and then that would create scores as below? But if I do that, how do I ensure that the same Score Metrics are used, ie the Record 1 in the 'Metrics Table'? 

 

2. In terms of reporting, for example in the case of "Apple, Metric 1 was more than 2, and the score was 178 - therefore 'Fail'". Would this be best done in a Table or through Report? 

Any advice/tips would be great! 

Thanks in advance!

  

 

 

 

 

 

 

[comment]

Can you explain what exactly is a "metric multiplier"? Where does it gets its value from, how often does this value change, and why are there 5 of them and not any other number?

 

[Neil Scrivener]

Hi, 

So Metric Multiplier is a number, which effectively weights the score. 

So Metric 1 may have a score between 1-10, but the importance of is then multiplied by the Metric Multiplier to give it we weighting of importance?

The 5 Metrics are just an example. In actual fact, I believe it's around 20.  

The Metric Multiplier is a fixed value. The only time that it would ever change, the global records from Day 1 should update as well as the most recent. 

Does help?

Thanks!

N

[comment]

So, if I understand this correctly, each item gets a fixed number of scores, and each score has its own multiplier. Structurally, this would translate to 3 tables:

Items --< Scores >--- Metrics

where the Metrics table (essentially a ScoreTypes table) has a field for the multiplier, and the Scores table has the actual score and a calculation field that multiplies the score by the value from the related record in Metrics. Similarly, the Metrics table can have a field for the minimum score, and the Scores table would perform the comparison.

However, Filemaker is not a spreadsheet, and showing 20 related records side-by-side (as opposed to one below the other, in a portal or in a report produced from the Scores table) is not as easy. 

 

[Neil Scrivener]

Hello, 

Yes, this is all correct. 

Showing the Scores doesn't need to be side by side, it can be in linear format. 

But, the Multiplier will only have one 'line' of data, which may be changed from time to time. 

Where as the Items will continue to be added. 

Could there be a way to add a repeat of Line 1 in the Multiplier for every time a Score is added - or is there a more simple solution. 

For clarity, the Item table would have an infinite amount of Records, as would the Scores Table, however the Multiplier would only have 1 Record. 

Does that help :)? 

[comment]

Maybe I still don't understand your situation. I was thinking of something like a school, where each student gets a score in a number of subjects - and each score is weighted according to its subject. 

 

[Neil Scrivener]

Yes, this is sort of correct. 

So item wise: 

English Score 10

Science Score 11 

Maths Score 12

Than you'd a Metric Multiplier, so for example: 

Homework x 3

Classwork x 2 

Field Trip x 2

 

This would get you a score of:

English, Homework = 30

English, Classwork = 22

English, Field Trip = 24

Total English Score = 76

 

Then Maths would be the same. 

It's not for a school, but I think this is the best way to explain it. Sorry it's complicated!  

 

 

 

 

[comment]

20 minutes ago, Neil Scrivener said:

Then Maths would be the same. 

How same would it be? Your model introduces an additional entity: the classification of a score as Homework, Classwork, Field Trip, etc. So the question is: should a score for a homework in Math be multiplied by the same factor as a homework in English? If yes, the subject is irrelevant to this discussion.

 

30 minutes ago, Neil Scrivener said:

It's not for a school, but I think this is the best way to explain it.

Perhaps you should try to explain the real thing anyway. Because in my experience these analogies are seldom perfect.

 

[Neil Scrivener]

Hello, 

Sorry, I think as well as badly explaining it, I've also messed the numbers up.

Does the Table below explain any better? 

The table is simplistic, and no way what I'd propose for this solution - but hopefully should explain how it works? 

ITEM:

English: Score 10

Science: Score 11 

Maths: Score 12

 

MECTRIC MULTIPLIER:

Homework x 3

Classwork x 2 

Field Trip x 2

 

		Homework	Classwork	Field Trip	TOTAL SCORE
	Multiplier:	3	2	2
ITEM	Basic Score
English	10	30	20	20	70
Science	11	33	22	22	77
Math	12	36	24	24	84

 

 

 

 

[comment]

 

But this is fundamentally different from your original question: here you have only one score per item, multiplied by different multipliers. Originally, each item had a distinct score in each category even before the multiplication.

I suggest again you explain what this is really about.

 

[Neil Scrivener]

Sorry, terrible at explaining in writing!

In essence it's financial information. 

So, the primary score is based on financial data, for example, revenue increases, profit increases, and so forth. The aim of the game is to determine a score of a company, based on information - and the Score will attempt to predict what the company will do in the future. 

So the Metrics to be tested per company are: 

- Revenue difference

- Operating Profit difference

- Earnings difference 

The multiplier is based on historic data, which weights the score, and sits in a 'History' table. This table can tell us based on a number of tests how that information has previously affected what the company is likely to do in the future. So tests may be:

1. Share Price Up + Revenue Up = 65%

2. Share Price Up + Profits Up = 15%

3. Share Price Up + Earnings Up =  20%  

That would in turn give a weighting on the importance of each of the Metrics to be tested. 

So if an item has a 23% increase in Revenue, the percentage would be turned into a Decimal, 0.23, which would be multiplied by 65%, that returns a 'score' for Revenue. The same applies for Profit, Earnings etc - the difference, turned into a Decimal, multiplied by a percentage.  

The end game is that each item has a score, and based on the results of the score, the higher the score the better the company will do in the future. 

Hoping that's as clear as mud? 

 

   

[comment]

Sorry, I don't know.

As a rule, if a company has 20 different attributes that need to be treated alike, I would tend to put them in a related table, linked by CompanyID. And if each attribute value needs to be multiplied by a number given by its type, I would also link it to a third table where each type has a unique record with its own multiplier value. This is basically what I suggested at first.

However, there are many factors that could change the way I look at this, and I cannot say I fully follow your description.