Arithmetic in Custom Column

[Soap-dodger]

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?

[BetterRed]

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

[Soap-dodger]

Originally Posted by BetterRed

See Arithmetic functions in Template language - Function classification

BR

Thanks for the link. I did have a look there and it did help somewhat.

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:

{:'and(field('#words'), strcmp(field('#words'), "", "", "", cmp(field('#words'), 0, "", "", field('#words'))), field('#pages'), strcmp(field('#pages'), "", "", "", cmp(field('#pages'), 0, "", "", field('#pages'))))'}

This seems to work perfectly, returning a "1" only when the division makes sense, else it is empty.

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:

{:'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')), "")'}

Apart from one use case, this works, returning a floating point value. I need to work out how to round the returned value to a whole number, though.

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:

EXCEPTION:  float division by zero

Can you help solve these issues?

[Soap-dodger]

Originally Posted by BetterRed

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

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.

[BetterRed]

@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

[davidfor]

Originally Posted by BetterRed

Hopefully one of the template experts, such as chaley, davidfor will chime in.

And personally, I hope chaley comes along first

But...

If it isn't obvious, I usually do it in program mode first. This is what I got:

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);

The idea is to get a non-zero number for the page count no matter what, and use zero for th word count if the page count was supposed to be zero. That gives zero for the words per page if there are no pages. But, maybe it should be the word count or something else.

In non-program mode this becomes:

{:'divide(cmp(raw_field("#pages"),0,0,0,raw_field("#words")),cmp(raw_field("#pages"),0,1,1,raw_field("#pages")))'}

And for the record, the first attempt was:

{#pages:'test($,divide(raw_field("#words"),raw_field("#pages")),0)'}

But, the "divide" is always executed even if the the first value is empty. I wasn't expecting that.

To get an integer, you can use "format_number" with an appropriate format template.

[thiago.eec]

Originally Posted by Soap-dodger

Apart from one use case, this works, returning a floating point value. I need to work out how to round the returned value to a whole number, though.

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:

EXCEPTION:  float division by zero

Can you help solve these issues?

Starting with the code posted by @davidfor, you could use something like the code below to format numbers as integers and use 'undefined' instead as '0', since those will appear on the book details pannel (that bothers me ).

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)

[Soap-dodger]

Originally Posted by BetterRed

@Soap-dodger
BTW the ifempty function will test for Undefined (null).

... for the hint.

[Soap-dodger]

Originally Posted by davidfor

To get an integer, you can use "format_number" with an appropriate format template.

You are so kind; your solution works excellently.

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:

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}");

Either the manual could be clearer or I'm missing something.

Please, can you advise where I've got it wrong?

[davidfor]

Originally Posted by Soap-dodger

You are so kind; your solution works excellently.

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:

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}");

Either the manual could be clearer or I'm missing something.

Please, can you advise where I've got it wrong?

The one that worked for me was:

format_number(divide(words,pages), "{0:3.0f}");

But, if you do it in template mode, you need to remove the braces. So, it would be:

{:'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")'}

[Soap-dodger]

Originally Posted by davidfor

The one that worked for me was:

format_number(divide(words,pages), "{0:3.0f}");

But, if you do it in template mode, you need to remove the braces. So, it would be:

{:'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")'}

Got it working now with your help.