06-24-2019, 03:50 PM | #1 |
Member
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Arithmetic in Custom Column
Is it possible to have a formula in a custom column that performs an arithmetic operation on the values of two other columns? For example, I have a custom column (#words) that contains the number of words in each book, and another custom column (#pages) that contains the number of pages in each book. I would like to have another custom column that shows the average number of words per page, so it would divide #words by #pages, provided both #words and #pages contain positive integer values. How might I achieve this? |
06-24-2019, 05:27 PM | #2 |
null operator (he/him)
Posts: 20,575
Karma: 26954694
Join Date: Mar 2012
Location: Sydney Australia
Device: none
|
See Arithmetic functions in Template language - Function classification
There's sticky at the top of this sub-forum that might have some examples of usage. And searches for 'arithmetic', 'math' and 'calculate' should find some more. If your #words and #pages columns originate from the Count Pages plugin you could create a private version of the PI and do the calculate in there - in python. Plugin zips contain the source code. BR |
Advert | |
|
06-25-2019, 06:12 AM | #3 | |
Member
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Quote:
I have almost got this working now, though I still have a couple of issues to iron out, and any help you might offer will be appreciated. First, in order to ensure that I do not attempt the division if either #words or #pages have null or zero values, I first created a temporary custom column called #ready, which has the following template: Code:
{:'and(field('#words'), strcmp(field('#words'), "", "", "", cmp(field('#words'), 0, "", "", field('#words'))), field('#pages'), strcmp(field('#pages'), "", "", "", cmp(field('#pages'), 0, "", "", field('#pages'))))'} Using #ready as a base, I then created another custom field to carry out the division and called it #awp (average words per page). This field has the template: Code:
{:'test(and(field('#words'), strcmp(field('#words'), "", "", "", cmp(field('#words'), 0, "", "", field('#words'))), field('#pages'), strcmp(field('#pages'), "", "", "", cmp(field('#pages'), 0, "", "", field('#pages')))), divide(field('#words'), field('#pages')), "")'} The only time it doesn't work is when both #words and #pages contain null values. In this case, I get the following error message: Code:
EXCEPTION: float division by zero |
|
06-25-2019, 06:19 AM | #4 |
Member
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Yes, both of my #words and #pages columns originate from the Count Pages plugin. I appreciate your suggestion regarding creating a private version of the plugin, however, my proficiency with Python is not yet up to the task.
|
06-25-2019, 06:37 AM | #5 |
null operator (he/him)
Posts: 20,575
Karma: 26954694
Join Date: Mar 2012
Location: Sydney Australia
Device: none
|
@Soap-dodger - I always have to hack templates by trial and error, so I won't try to answer your questions but I would have tried to use a Program Mode template. BTW the ifempty function will test for Undefined (null).
Hopefully one of the template experts, such as chaley, davidfor will chime in. BR |
Advert | |
|
06-25-2019, 07:53 AM | #6 | |
Grand Sorcerer
Posts: 24,907
Karma: 47303748
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
Quote:
But... If it isn't obvious, I usually do it in program mode first. This is what I got: Code:
program: pages = cmp(raw_field('#pages'),0,1,1,raw_field('#pages')); words = cmp(raw_field('#pages'),0,0,0,raw_field('#words')); divide(words,pages); In non-program mode this becomes: Code:
{:'divide(cmp(raw_field("#pages"),0,0,0,raw_field("#words")),cmp(raw_field("#pages"),0,1,1,raw_field("#pages")))'} Code:
{#pages:'test($,divide(raw_field("#words"),raw_field("#pages")),0)'} To get an integer, you can use "format_number" with an appropriate format template. |
|
06-25-2019, 11:05 AM | #7 | |
Guru
Posts: 928
Karma: 1177583
Join Date: Dec 2016
Location: Goiânia - Brazil
Device: iPad, Kindle Paperwhite
|
Quote:
Code:
program: pages = cmp(raw_field('#pages'),0,1,1,raw_field('#pages')); words = cmp(raw_field('#pages'),0,0,0,raw_field('#words')); wpp = format_number(divide(words,pages),'{0:.0f}'); cmp(wpp, 0, '', '', wpp) Last edited by thiago.eec; 06-25-2019 at 03:07 PM. |
|
06-26-2019, 03:03 AM | #8 |
Member
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
|
06-26-2019, 03:53 AM | #9 | |
Member
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Quote:
I am having difficulty in converting the result to an integer. I have read the relevant section of the Calibre manual to no avail. Here's what I've tried so far: Code:
format_number(divide(words,pages), {0:3.0f}); format_number(divide(words,pages), "{0:3.0f}"); format_number(divide(words,pages), {0:,d}); format_number(divide(words,pages), "{0:,d}"); Please, can you advise where I've got it wrong? |
|
06-26-2019, 08:01 AM | #10 | |
Grand Sorcerer
Posts: 24,907
Karma: 47303748
Join Date: Jul 2011
Location: Sydney, Australia
Device: Kobo:Touch,Glo, AuraH2O, GloHD,AuraONE, ClaraHD, Libra H2O; tolinoepos
|
Quote:
Code:
format_number(divide(words,pages), "{0:3.0f}"); Code:
{:'format_number(divide(cmp(raw_field("#pages"),0,0,0,raw_field("#words")),cmp(raw_field("#pages"),0,1,1,raw_field("#pages"))), "3.0f")'} |
|
06-26-2019, 09:00 AM | #11 | |
Member
Posts: 14
Karma: 10
Join Date: Dec 2016
Location: United Kingdom
Device: Amazon Kindle PaperWhite 3
|
Quote:
|
|
Tags |
arithmetic, custom column |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Adding custom column with any lookup-name gives me empty column as a result? | Ahu Lee | Library Management | 16 | 06-09-2019 12:14 PM |
Move selected data from series column to a new custom column | fvdham | Library Management | 3 | 06-02-2017 03:49 PM |
Custom column returns value based on value of another custom column? | calvin-c | Calibre | 3 | 09-14-2013 02:24 PM |
Custom yes/no column built from long text column | Philantrop | Library Management | 7 | 03-23-2013 07:44 PM |
how to move value(s) of tag column to a custom made column | zoorakhan | Library Management | 0 | 12-08-2012 03:53 AM |