What’s new?
jQuery Calx version 2.0 is the successor of jQuery Calx 1.x. It offer a lot of new feature since version 1.x, let see what’s new in jQuery Calx 2.0
- Rewrite the internal formula parser and plugin structure.
- Ability to register new function and override default one.
- Ability to register variable.
- Easy graph to represent data in graphical way (additional library required)
- Add support for string and cell range.
- Add a lot of excel compatible function (additional library may be required).
- Add feature to check circular reference, instead of infinite loop which make the page hang up, it’s now alerting error.
- Add support for server-side function formula, in case you want to hide the calculation algorithm.
- Add support to change formula, value, format easily without need to write tricky script.
To support us developing more feature on jQuery Calx 2.x, you can fork and send a pull request on the github repository https://github.com/xsanisty/jquery-calx or you can send a donation to us.
Updating from 1.x
If you are using jQuery Calx 1.x and want to update to 2.x, here is some points you need to take care of.
Replace id with data-cell for cell addressing
jQuery Calx 2.x use data-cell attribute to set the cell-address, since there are possibility of two or more same cell-address in single page
<!-- previous --> <input id="A1" /> <!-- current --> <input data-cell="A1" />
No more $ sign in formula
In jQuery Calx 1.x, we use $A1 to get the value of cell with address A1, in 2.x we completely remove the $ sign and use the cell address directly to access it’s value
<!-- previous --> <input data-formula="SUM($A1,$B10)" /> <input data-formua="$D8-$D5" /> <!-- current --> <input data-formula="SUM(A1:B10)" /> <input data-formula="D8-D5" />
HI,
First of all , you did a great job with this libray!!!
However we face some problems with keyboard keys when entering a cell and trying to suppress its velue
sometimes it selects all (mouse move) and sometimes not and more if cell is selected with mouse mouve we can not delete its content.. it reversts back to its prvevious value…
how can we solve it
many thanks in advance!
Hi Nicog,
regarding to moving through form element using a tab key, I don’t face such problem, the value is always selected.
regarding to content suppressing, that behaviour is by design, calx is designed to build a fixed calculator using excel like formula, so we can’t change the value of a cell that has formula, it’s value will always be replaced with the calculation result.
regards,
Ikhsan
Hey there, so I am trying to get this to work but I’m stuck.
I have a formula already assigned to my cell (textbox) but when I change the select button I need it to calculate different cells. How can I replace the formula to the cell? Thanks for your help!!
Hi Julio,
if your select box is still under calx control, you can give your select box a cell address and refer to it
Regards,
Ikhsan
Hi
I have table rows being generated by angular. In the table rows, I specify the data-cell and data-formula from unique keys. But whenever I try editing any of the field, the get this error:
Uncaught TypeError: Cannot read property ‘getValue’ of undefined
Uncaught TypeError: Cannot read property ‘renderComputedValue’ of undefined
Uncaught TypeError: Cannot read property ‘isAffected’ of undefined
Hi Babantude,
It seems that calx trying to access element that already deleted or so, try to run $(‘#id’).calx(‘update’) each time the table is updated.
but, I recommend you to build some kind angular directive or controller for processing the calculation there instead of using calx as it will be easier I think
regards,
ikhsan
Hi Ikhsan. Thanks for the swift response. I eventually got it to work by building a directive to detect when the ng-repeat has finished rendering and calling jQuery(‘#progressWizard’).calx(); so it can run through the new form elements. But then I have another issue popping up.
So I have lots of references like so
So there is an input that has data-cell and another input referencing it. But it seems not to work.
Hello! Can i use other value, for example rell or something like this?
Now
i need
Hi Misha,
I think you code is truncated, you can use ‘crayon’ button on the comment form to insert some code snippet
Hi ikhsan,
I have problem adding Calx to bootstrap modal.
Do you have some advice?
Thank you.
Hi Rde,
any error popped out on console? or do you have any sample online?
happy new year btw 🙂
I’m trying to set value to cell using setValue :
$calx =$(‘.table’).calx();
$calx.calx(‘setValue’, ‘B’, ‘some’);
On console, Uncaught TypeError: c.getCell(…).setValue is not a function.
Thank you and happy new year u2…
Rde,
I think it is because you want to set value of non-existence cell address
also, your selector .table could return more than one element
Hi, thanks for the great tool.
I have a 15 sheet huge document coming from excel with about an 1000 complex formulas everything works as expected but the render time in the browser is about 30-90 secs (depends on the client computer). in your opinion Is it possible to use something like phantomjs to prerender the document on the server side?
Hi Malhavoc,
I don’t have any experience with phantom.js, so i don’t know if it is possible to render it using headless browser. In my experience, the biggest sheet I have ever done contain around 1700 cells, and the calculation can be done in 2 to 3 secs, but it depends on how complex the formula and how big the formula graph.
I see that the performance of calx can be quite slow on first render because calx try to calculate all available cell with formula.
Hi , trying to optimize initial load time i have commented out the calx.sheetRegistry[sheetIdentifier].buildCellDependency(); function on line 9808 now the initial load time is about ten times faster and i do not see any problem with calx funtioning. What is the purpose of this funcion and can i run calx without it safely ?
That is used for building dependency graph between cell based on their formula, eg, C1 has formula of A1+B1, then A1 and B1 will be added to C1 dependency list, and C1 will be added to A1 and B1 dependant list.
on auto calculate mode off, when C1 is calculated, it will trigger A1 and B1 (cell within C1 dependency list) to be calculated first, and continue calculate C1 itself when all dependency is calculated.
on auto calculate mode on, the process is vice versa, when A1 or B1 got updated, it will trigger C1 (cell within B1 and A1 dependant list) to be calculated.
if that line is removed, the calculation result maybe incorrect when the formula graph is complex, but its ok when auto calculate mode is off and no formula depend on other formula.
THis is really great plugin… I just used this plugin on local server. but when i uploaded the same on my web server it is not working it is showing error $form.calx(‘getCell’, ‘G1’).calculate() is not a function; i dont know what i m doing mistake..?
make sure you are not uploading the jquery-calx-sample 🙂
I uploaded complete folder please check this link
https://royalsun.biz/jquery-calx-master/jquery-calx-master/sample/
the same is working on my local server but unfortunately not on web server
Yes, as I already said before, make sure you are not uploading the sample version of jQuery calx, as it was designed to only run on localhost or xsanisty.com 🙂
you still can use the jquery-calx-2.x.x.min.js or jquery-calx-2.x.x.js, and not jquery-calx-sample-2.x.x.js
thanks ikhasan
Hi,
how can I get data-cell id on table row click? For example second column data-cell ID?
It is dynamic table.
Thanks.
Hi Rde,
I think you can simply use jQuery for this, something like
How to I add this excel formula in calx?
=IF(B1=1000,B11300,B1<=2500),(B1*5.1),(B1*4.85))))
Hi Brian,
what is that formula trying to do? I am a bit lost here 🙂
regards,
Ikhsan
When I attempt to paste the exact excel formula into the comment box, it get’s reformatted such that you can really see what I’m trying to do. You can download the excel ss I’m attempting to duplicate in calx here: http://www.hemmaconcrete.com/calx.html I am happy to compensate you for your assistance.
Is this the sample formula, right?
you can just use that formula with calx 🙂
e.g
regards,
Ikhsan
That’s fantastic! What am I doing wrong with formatting (I need dollar sign, two decimal places and a thousands separator) and how can I set this up such that a submit or click is required to perform calculation? Thanks again so very much for your assistance!
http://www.hemmaconcrete.com/driveway_price_calculator_rev.html
Regarding to formatting, I think you miss the numeral.js, you should load it before calx.
to perform manual calculation, you can disable the autoCalculate feature, you can refer to the ‘configuring jQuery Calx’ page for the documentation.
and on the calculate button, you can put something like
or you can even put a reset button
Hi another quick question.. I created my first calculator thanks to your amazing plugin…
now i wanted to integrate it into my simple wordpress website, but it doesnt work…
I included all headers and the same code… is there a solution for that?
thanks so much!
i feel so stupid…
it works PERFECTLY ! on wordpress.. just add the script like that into your page/post
Kr,
on wordpress, maybe you can
Hi, really nice plugin.
Is there a possibility, to listen to an output and work with the condition.
Lets say result of A1+A2=20 –> run some jquery or show something
Or A1+A2>30 –> run some other code or show some other text
thanks
Hi Kr,
Thanks!
You can use the onAfterCaculate event, assume your field with formula A1+A2 is given address as B1
wow ikhsan.. thanks a lot.. once my calculator is ready, ill share it here.. 🙂
this is so great!
Is there a way to automatically return 0 if I reference an empty cell? Just like in the excel.
Sergio,
maybe you can try something like
IF(A1='', 0, A1)
I am working with a form that uses your fantastic plugin.
The problem that I have is that the data is not being placed into the database. Below is a sample input from the form that is failing to insert the data into the database.
Shipping allowance paid today:
Housing advance:
Travel and settling-in:
Other 1:
:
Other 2:
:
Hi J.
I am sorry, but seems your code is truncated, could you use the ‘crayon’ button to add some piece of code?
regards,
Ikhsan
Career Inbound Part A
.no_top_margin { margin-top: 0; }
Financial Arrangements
Personal Funds
Prorate by days:
days
Personal Amount:
=
COLA Amount:
=
Bonus Amount:
=
One month personal support:
One month service bonus:
$(‘#inbound_a’).calx();
Personal Funds
Prorate By Days:{{prorate_number_days}}
Personal Amount: {{prorate_personal_amount}}
Personal Total: {{prorate_personal_total}}
COLA Amount :{{prorate_cola_amount}}
COLA Total:{{prorate_cola_total}}
Service Bonus:{{prorate_service_bonus_amount}}
Service Bonus Total:{{prorate_service_bonus_total}}
Personal Support (One Month) :{{prorate_personal_support}}
Service Bonus (One Month) :{{prorate_service_bonus}}
Grand Total:{{prorate_total_a}}
Hi Ikhsan,
I am quite new to PHP and your plugin saves a lot of time and is very handy, thank you!
However some problems occur,
Generally i need to have a table which will load numbers from DB like:
for ($j=0; $j < $controlsrows; $j++ ){
$non_neutral_country_risk_controls= mysqli_fetch_array($resultcontrols, MYSQLI_NUM);
if($j<10){
echo "
$non_neutral_country_risk_controls[0]
$non_neutral_country_risk_controls[1]
$non_neutral_country_risk_controls[2]
$non_neutral_country_risk_controls[3]
$non_neutral_country_risk_controls[4]
$non_neutral_country_risk_controls[5]
$non_neutral_country_risk_controls[6]
$non_neutral_country_risk_controls[7]
“;}
And by givin name to each cell i want to use VLOOKUP to search for this value and it does not work.
Example of the call:
data-formula=’MIN(80%;IF(OR($countryvar<0;60%;IF(AND(VLOOKUP(F1;M100:N118;TRUE)<8;VLOOKUP(F20;M100:N118;TRUE)7.5;VLOOKUP(F1;M100:N118;TRUE)8.5;0.15+0.006*((VLOOKUP(F20;M100:N118;TRUE)-VLOOKUP(F1;M100:N118;TRUE))/0.5);0.6+0.028*((VLOOKUP(F20;M100:N118;TRUE)-VLOOKUP(F1;M100:N118;TRUE))/0.5))))))’
F1 and F20 correspondigly are two data-cells which are recieving values from previous calculations and based on their values, this data-formula will calculate the result.
Hope you will see this post!
Thank you!
Hi Valentin,
Glad it help on your project,
by the way, the delimiter in formula is comma (,) not semicolon (;), maybe you can change it first.
also can you provide the generated html table instead of the php code?
regards,
Ikhsan
Hope you can help)
As far as i understood you asked for this piece, and i have tried to change delimiters and it works kind of strange ( in some formulas it started to show #N/A which may be typo or something like this) However thos formula (big one) is not working.
Thank you for your time!
hi Ikhsan,
Quick question. Is it possible to do a screen print function or download into excel with the form values?
Thanks
Paul
Paul,
window.print() method should do the task, but to generate the excel file, I think we will need other library
Mas Ikhsan saya sudah berhasil untuk nampilkan grafik, tetapi kalau pada data-cell saya pakai input type number dengan menggunakan spin up-down kenapa gak bisa update yah grafiknya. Tapi kalau value saya ketik manual bisa update langsung. Mohon penjelesannya, terima kasih, Oh ya contoh yg saya buat ada di blog http://gurukimiakalteng.blogspot.com/2015/05/tes22.html
di bagian comment kayanya kemaren ada tanya hal yang sama, coba pake ini
Ok terima kasih mas siap saya coba ni (Y)
Mas naruhnya gimana, kan kode yg sy pakai begini:
$ ('#grafik').calx({
data : {
A1 : { value : 'Bahasa Indonesia' },
B1 : { value : 'Kimia' },
C1 : { value : 'Fisika' },
D1 : { value : 'Biologi' },
E1 : { value : 'Matematika' },
F1 : { value : 'Bahasa Inggris' },
A2 : { value : 0 },
B2 : { value : 0 },
C2 : { value : 76 },
D2 : { value : 89 },
E2 : { value : 90 },
F2 : { value : 100 }
}
});
Mohon pencerahannya, maturnuwun 🙂
Saya coba aplikasikan belum berhasil juga mas, sy makai input type number dengan spin updown itu.
Hi Ikhsan,
This is great! Thanks for this.
Is there a way to get right-click mouse paste to trigger the calculation?
When I paste a number into a textbox, it just resets to the value that was previously there.
Hi Kevin,
you can try to bind the form field to listen on paste event
regards,
Ikhsan
Hi Ikhsan
Is there a length limit ?
I use severals IF function indented and i have an error message.
Thanks
Hi Olivier,
There should be no limit for the formula, could you paste your formula here?
Regards,
Ikhsan
Sorry, you can delete my comment.
I just find my error.
Hello again,
Thanks again for the excellent and useful plugin
I am facing problem while rounding a number to two digit after decimal like 25.22.
You can see code attached on fiddle.
http://jsfiddle.net/mqeb6q8n/
I have added data-format attribute in Formula field, but its not rounding number as I like.
Reading last comment, i did have added numeral.js.
Still not working.
http://jsfiddle.net/msadiqrajani/mqeb6q8n/2/
It seems your numeral.js is not actual numeral.js
http://yourjavascript.com/11102553393/numeral.js
Ahhh…Yes.
I was adding wrong Numeral.js.
I tried after downloading from site http://numeraljs.com/
Its working now..
Thank you very much.
Hi Ikhsan,
As your guidance I have to use jquery-calx-2.1.1.min.js and jquery-1.9.1.min instead of jquery-calx-2.1.1.sample.js now its work fine. but there was no line to set Currency and data-format=”$ 0,0.00″ not working.
Subhamoy
You need to include numeral.js before loading jQuery Calx
Hi Ikhsan,
Kindly take a look http://srihrd.com/all/basic.html but its not work perfectly
I place basic.html and css js folder from jquery-calx-2.0.3
in css folder=bootstrap , sb-admin and style.css
In js folder =bootstrap and jquery-calx-sample-2.0.0.min.js
is this okey ? or I have to do something?
Hi Ikhsan,
Thank you for your quick response…
Kindly provide me url of release version of Clax
You can find it in the left sidebar, use jquery-calx-2.1.1.min.js instead of jquery-calx-2.1.1.sample.js
Hi, I am very new in clax, I have a problem with clax
On my localhost system jquery clax working fine, but when I place my project on a server and access it using url its not working properly, show hide formula working fine but I cant display any result (Total Balance).
Kindly help me
Thanks Subhamoy
Maybe you are using the sample version of calx which can only run on localhost, try to replace it with the release version
regards,
Ikhsan
is the AND operator working the same as with 1.4x for 2x?
IF(D1>0 AND E1>0,L1+R1,L1+O1)
my real question is multi-depth for AND example
IF(D1>0 AND E1>0,L1+R1,IF(D1>0 AND E10,L1+R1,0)))
is that possible?
actually, i was adding the AND incorrectly… it works this way
IF(AND(D1>0,E1>0),.....
Hi Ikhsan,
I have Dynamic Form and I want to calculate Total at the bottom of the form.
Formula of calculation is complex.
(A1*B1*(1-C1/100))*(D1/100)
…
(A ‘+i+’*B ‘+i+’*(1-C ‘+i+’/100))*(D ‘+i+’/100)
Is it possible to calculate the sum directly or do I calculate separately for each row and then e.g. SUM(H1:H’+i+’).
Regards
I’ve tried e.g. SUM((A1*B1*(1-C1/100))*(D1/100): (A ‘+i+’*B ‘+i+’*(1-C ‘+i+’/100))*(D ‘+i+’/100) but does not work
Hi Eni,
It should be ok to sum multiple range, like
SUM(H1:H10, G1:G10)
orSUM((A1*10), G1:G10)
Hi Ikhsan,
sorry for the stupid question, but I have problem with Mysql. From the table, get the value of the product for the fields A1 and A2 but A3 field is not calculated. only when I manually type values in the fields A1 and A2 calx2 works.
Maybe I need to change Default Configuration. I do not know .
regards and thank you
Hi Eni,
In the select callback, you should not set the value of #quantity_1 and #price_1 directly, instead use the calx api, you can try following snippet
regards
Ikhsan
Hi Ikhsan,
all works very nicely. thanks for the help. all the best.
regards and thank you
Hello, I am trying to get a cell (MS1) and update its value to 0 – what am I doing wrong??
I have tried allsorts.
Thank you
Hi Ally,
It seems no problem when setting up the value, you can try to check this page http://prototype.xsanisty.com/calx2/checkbox.html
and run code below in the console
If I try this:
When I write the variable allycell it still gives the original value not 0.
It seems you want to change html content of some element outside calx container when the calculation is updated
try onAfterCalculate callback
again, you can open the sample page, and run code below in console
the value of someVar is 234, which is correct
Hello,
could you perhaps contact me? i need an jquery online calulator tool for our site.
Perhaps you can develop such a tool.
thanks,
Regards,
Olaf
Hi Olaf,
just sent out a message to your email, or you can contact us at developers@xsanisty.com
hi ikhsan,
is it possible for you to implement the new html5 number-input validation for max/min quantity-checks? . -> http://www.w3schools.com/tags/att_input_max.asp
thx, y
Hi Yellow,
Not sure how it should be implemented, I think browser will validate the input automatically when the max-min attribute is defined, calx will just get and set the value according to the calculation result.
regards
Ikhsan
In case you want to limit the input and reset back to max or min value, maybe you can use onBeforeCalculate trigger
I didn’t try it yet, since the plugin didn’t have any automated test yet
hi again ikhsan i found problem can you help me to solve it please
hi, i made a php page from sample excel file. excel file have a lot of calculations with cells. after i add all calculations page gives me this error on console.
Uncaught RangeError: Maximum call stack size exceededjquery-calx-2.0.5.min.js:1
v.fx.isProcessedjquery-calx-2.0.5.min.js:192
Also i cannot sum field while if i add it to another calculation ex;
how can i fix this erorrs? thanks.
Hi Turker,
it seems you have a very large sheet, or some complex formula tree, could you try to check if there is a circular reference?
If nothing found, maybe you can send the html page to me, so I can reproduce the error on local side
Uncaught Error: Circular reference detected, this may cause calx to stop working.
cell : F44
formula : F44*((G20+G33)/((100-G4)/100)/((100-G5)/100))/1000
Please check each cells involved in the formula that has direct or indirect reference to F44
hımm i will check this formula
F44 can not use itself in the formula, it will make an infinite loop since once formula is calculated, it will refer to F44, once F44 is referred it will calculate itself which will refer to F44 again, it seems the world will collapse if you do that 😀
yes 🙂 i give twice input name corrected it. but other problem continue.
Uncaught RangeError: Maximum call stack size exceededjquery-calx-2.0.5.min.js:1
v.fx.isProcessedjquery-calx-2.0.5.min.js:192
this error was my fault and i corrected it thanks .>
but i cannot use calculate this
Hi Turker, you could use the ‘crayon’ button to insert some code 🙂
sorry. my other problem is continue. by the way thanks for fast replys.
Hmmm, seems strange, how could I reproduce the issue so I can debug it on my side?
I love this amazing plugin.I have one question.
You can see in this example, When I change the value of red text box, it does calculates when I move focus from that text box.
What I am trying to do is to calculate as soon I change value without moving away focus.
Link to fiddle:
http://jsfiddle.net/msadiqrajani/n7X7b/6/
Hi Sadiq,
Yes, you can do it in jQuery Calx 2.x by setting the autoCalculateTrigger option
Thanks…Its working.
Hi,
I was wondering, for server side calculations, would it mean running Calx2 on for example Node.js?
Best regards,
Lau
Hi Lau,
No, it only send the request to the server containing information about the function name and list of parameter. It is used if you don’t want to expose the calculation algorithm to the end user.
you can use any server side language to do the calculation
for example, you have cell with formula of
SERVER('someSecretCalculationFunction', A1, A2, A3)
, assume that you configure calx with ajaxUrl pointed toformula_processor.php
, in the formula_processor.php, you can define the calculation based on the received requestThank you, so if I understand correct this way it will lose the usage of Calx2 as the calculator,
but PHP calculation libraries can be used to return the result.
Triggering of the correct fields etc. will still be handled by Calx.
You can still mix the both of them, except you want to hide all possible calculation formula
regards
Ikhsan
Perfect I will keep watching. last question, can your script call values from an external source i.e. url or currency exchange?
Thanks again,
Ric
You can use the ‘onBeforeCalculate’ callback to retrieve data from currency exchange server, and set some cell value before calculation is preformed, or use server() formula to retrieve the data from your backend
Regards,
Ikhsan
Sorry for the stupid question but can you give me an example?
What I am trying to do is
1. User selects currency then types the amount in a text box.
2. Adding all values in a Subtotal field
3. Calculating the exchange on the total field based on the currency select drop down in #1.
If you answer this I would praise you forever. And I promise no more questions. LOL
Ric
Lol, it is almost midnight here and I’m replying from my mobile
Will try to build an example tomorrow morning,
Btw, which exchange server would you use?
I will be using xe.com
And sorry about keeping you up late
Have a good rest there 🙂
And for sure Iw ill spread the word on your amazing script./ Nyt nyt
Ric
Hi Ric,
It seems I can not find the documentation for retrieving the exchange rate via jsonp or else, could you point me to the link, or maybe I can create sample using open exchange rate
regards
Ikhsan
https://openexchangerates.org/documentation
Is the closest thing I can find
Amazing! Thanks I was trying to use vlookup but your solution was way better.
Thanks again and keep up the good work. Can I subscribe to your new versions?
Ric
You are welcome!
you can always watch on the github repo here https://github.com/xsanisty/jquery-calx to get notified about any change, including version release
regards
Ikhsan
Great script!!1
Was wondering how to handle drop down lists? I did not see it on the examples page.
Thanks
Ric
Hi Ric,
Thanks 🙂 , you can place the data-cell attribute into the select tag, data-format and data-formula are not processed in select tag
Regards
Ikhsan
Wow
Hi,
Any news about the new version?
Thank you
Hi Liviu,
The new version is completed, and after months of development and weeks of review, we decide to release it under MIT license 🙂
regards
Ikhsan
Hi, I just came across your amazing tool. I would like to use version 2 to start our project as opposed to v1. Can you let me know what the status is so I can purchase?
Hi there, if Version 2 of Calx is available i will buy this. Because it is a good tool. Customer is waiting 🙂 Regards Andi
Hi Andi,
Thanks, we are still incubating the version 2, it’s 98% done, and still writing the documentation intensively.
We are working hard to release in this weekend
regards
Ikhsan