How to sum up entries of one column and use that sum for calculations in another column to create a list of my grades with the grade point average?

[Nahele]

Hi,

I would like to use Memento to keep track of my grades. To realize this idea I need to sum up the entries of a column and use this sum for some calculations in another column. It is a bit difficult to explain, especially for me as a non-native speaker. That is why I attached a sketch that visualizes my idea and what I want to sum up. I really hope anybody can help me to solve this issue. 

Best regards

Alex

[Eugene Kartoyev]

Android. For that purpose you will need to make two libraries.

Library 1
Name: Exam
fields:
Exam - Text
Grade - Real
CP - Real
GradeByCP - calculation: #{grade}*#{cp}

Library 2
Name: GPA
fields:
AcademicYear - text, or whatever you want.
Subjects - Link to entry - many to many - library Exam
GPA - calculation field #{subj...@sum.gradebycp}/#{subj...@sum.cp}

Fill in Library 1 with your exam names, grades and grade weights

In library 2 enter Academic year 2016 as the name of the field, for example.
In the link field, select the exams you want to be included in your GPA.

Close the editing mode and see the result.

For esthetics, in the *first* library called Exam - you can edit the link fields and make them invisible.

[Bill Crews]

For those of us inn the remedial class, you might suggest to us the meaning of CP and of ProSalem.

[Eugene Kartoyev]

Come on, Bill! I'll never believe you needed a remedial class!

CP is credit points. Normal people would call it weight coefficient used in calculation of weighted average.

ProSalem is actually "Problem" you misread :)

To calculate Great Point Average (weighted average of grades), simple arithmetics is used:

Weighted Average of Values = Sum (Value i × Weight i) / Sum (Weight i)

Or GPA = Sum (Grade of subject i × Credit of subject i ) / Sum (All credits)

[Bill Crews]

I learned early in my academic career NEVER to enroll in classes that start before 9:30 am. I am clearly not awake until that hour. ;-)

Thanks, Zhenya (still guessing).

-bc

[Nahele]

Thank you for your explanation! That works fine so far but I have got some new questions and I hope you can help me with them as well.

1. Is there a possibility to realize this in only one library?
2. It is more important for me to know the GPA of all semesters altogether. Is it possible to calculate the GPA for all semesters (in your example: for more than one Academic year) within the GPA-Library? Of course, I could assign all exams to one academic year and think of another name instead of "academic year" but maybe there is a better way?

Thanks in advance.

[Bill Crews]

You didn't say what compels you to have only one library. Some people have a visceral aversion to linking libraries. Others think they can sort, group, and filter only if everything is in one library.

The fact is that there are prices to pay for designing against nature. The downsides of cramming into one library are several, but mainly include redundant data that is then hard to update when needed and hard to keep in sync among the copies.

The thing is, once libraries are linked, you can both aggregate across the libraries and you can reference fields in the linked library from the linking library, so you can still do all your slicing and dicing from one library without having to duplicate the data.

I suggest the following libraries: Teachers, Students, Courses, Terms, Classes (association of a teacher taking a course during a term), Grades (per student per class). Then, you can suck everything into Grades from the other linked libraries and slice and dice to your heart's content, but you can update everything in its natural place.

[Eugene Kartoyev]

Yes, Bill has given you an excellent insight about databases philosophy. The problem is that you need summing entries.
You cannot sum up other rows inside a row of the table.

It would sound like getting oneself out of a pit by pulling one's own hair.

In one library of any standard database, all you can do with rows to summ them is to get data into a separate report. In Memento this is realized with the help of aggregation. Aggregation has 4 functions: summing fields, finding average, min and max from all fields. And aggregation results are not accessible to particular fields because aggregation is done after fields are entered. A field must be enetered first before getting aggregated, and never the other way around.

Thus, as an option -- you can sum up the fields by aggregating them in Memento and then use a calculator to divide total grades×credits by total credits. No need for a second table.

But, if you do not want to use a calculator, as I workaround, you can make another table, which will work with the ready for aggregation fields from the first table, get the fields which must be aggregated with your help, and then do the calculation. In which case you will need to establish a link to every field which is taken into account in aggregation. Like you said, don't call it Academic year, call it All Semesters of All times, and then show all the exams you took during that period.

[Eugene Kartoyev]

Aggregation

[Eugene Kartoyev]

By the way, Bill! In the picture I attached in the previous post, the user needs to divide the first aggregated field by the second aggregated field.

Sometimes people need other operations between aggregated fields... Now, other operations on aggregated fields are accessible only kf you use a second table.

Wouldn't it be great to propose the programmers to extend the functionality of aggregations as to be able to make operations between aggregated fields too? A kind of Calculation field for the aggregation row...

I know it will be quite easy to do, considering that Memnto has already calculation and javascript functions. All they need to do is to add these same calculated functions to the row of aggregations. That would resolve a great many issues of making final reports, and will be a big step to make memento produce database versatile consolidated reports...

[Bill Crews]

Library Grades
- field Student type Text role Name
- field Self type Link To Entry type many-to-many library Grades
- field Grade type Real role Status
- field Credit Points type Real role Status
- field Curve type Calculation role Status
- result type Real scale 5
- script "(#{grade}*#{credit points}) / #{se...@sum.credit points}"

The aggregations are not needed, but probably desirable:
- aggregation Sum field Grade
- aggregation Sum field Credit Points

The values agree with Alex's. You do have to populate the link for each entry, though.

[Eugene Kartoyev]

Bill, that's an interesting one. I didn't ­know a table can refer to its own self. Th­nk you. Still, one needs to think on how ­to automate adding new links. I saw a request for that in uservoice, but it has very few voices, so I guess it's not a priority currently.

[Yan Lin]

Hi Bill...
I tried to use self@sum to aggregate all the values in my library, but it didn't work.
Can you give me more information regarding this aggregation method?
Thank you

[Bill Crews]

That was a year and a half ago. As I recall, I wanted to aggregate over a set of entries within the current library, so I created a many-to-many link-to-entry field pointing to the current library and called it Self. Then, I could sum over the entries of the link using something like Self@sum.

After so long, I can't tell you more without going back through and reconstructing it.

[Adi Kwok]

hello seniors.

please help,
im stucked to this sum field in the library itself.

i tried to do advise from eugene and bill, from this forum and wiki, but still get stucked

cant get sum total of field, without using aggregation.

just need to be able to sum the 1 field ( column ) to make calculation based on that summed value.

like this:

item |qty |unit price |amount
pen | 1 | 10 | 10
book | 2 | 20 | 40

total amount:*stucked | 50

how to make that?

thx sirs and best regards
adi kwok.

[Bill Crews]

There are several ways to do it; which works best depends on what you're trying to accomplish. I'll just pick one...

Field amount, type JavaScript, script...

field("qty") * field("unit price")

So, now, where do you want this total of the amounts? Storing it as a field doesn't make sense, because its value is a total across all entries, not a value of a given entry. But you say you don't want aggregation? But that's where such a total belongs.

If you want code, I can write a quick action to calculate the total, but what? Write that to a file? Or what? Here's the code for a library action called "Sum of amounts"...

var lib = lib();
var entries = lib.entries();
var sum = 0;
for (ent = 0; ent < entries.length; ent++)
sum += ent.field("amount");
// OK, now what? Since I don't know what, I'll display a message with the total.
message("Sum of amounts is " + sum);

I think you really want to use aggregation but think you don't for some reason. With aggregation, the total will show up in all lists of entries, if filtering, then only for the filtered entries, if grouping, then a grand total plus a subtotal for each group. Why don't you want to use aggregation? I recommend it.

[Adi Kwok]

thx for your brief sharp explanation, advise and recommending. sir.

this memento aggregation is rock.
especially when use with group, filter and sort.

your recommending is highly precious and appreciated from me. bill, sir.

now i knew that aggregation is doing the things more than what im looking for. it was your replied which made me knew about ' grand total by each separated subtotals '

best regards.
adi kwok

.

.