math calculation

Discontinued products Uncategorized discussions
#1

I used Math formulas with Script to calculate the product of two columns (price quantity)

---- mygrid.setColTypes("ed,ed,ed,ed[=c1c2]");



Also, I make a subtotal to sum the value of three rows by using math calculation in xml file.The coding is below:

when i input new values in price column and quantity column, the subtotal will be updated.However,there is no change in "amount "column where I set Math formulas here. why the product of column 1 value and column 2 value is not changed?



THANK A LOT





<?xml version="1.0" encoding="UTF-8" ?>





100%

1.0

2.00

2.00









100%

3

4.00

2.00









100%

1

0.5

2









subtotal

=[[r1,1]]+[[r2,1]]+[[r3,1]]

=[[r1,2]]+[[r2,2]]+[[r3,2]]

=[[r1,3]]+[[r2,3]]+[[r3,3]]

















--------------------------------------------------------------------------------------------------------------------











    For demo purpose only :: &1

    







    

    

Math calculations



    



Calculate values with Math formulas. Edit formulas on the fly

    



    

    

            

    

    

    



            














#2

XML with data shouldn’t contain values for cells which should be calculated. In other words your XML should be as follows (it seems you just forgot to delete them, as you have more cell tags per row than expected for some reason):


<?xml version="1.0" encoding="UTF-8" ?>


100%
1.0
2.00





100%
3
4.00





100%
1
0.5





subtotal
=[[r1,1]]+[[r2,1]]+[[r3,1]]
=[[r1,2]]+[[r2,2]]+[[r3,2]]
=[[r1,3]]+[[r2,3]]+[[r3,3]]



#3


Our company bought one  license of professional version of your product. When we are using your product,in our development project, we face some technical problems about math calculation.



We make one subtotal to sum the value of each row   and  the product of two columns (Price column* Quantity column=Amount column)   respectively using dhtmlgrid 's math formula .When enter some values quantity column or price column, it is expected the values in Total and Amount column will be updated immediately. However ,amount values are NOT UPDATED .Our development project is in urgent production phase. Please answer our inquries as fast as you can. Thank a lot.  The following sample.zip  file is for your reference .


samples.zip (160 KB)

#4


I get another grid calculation problems



I used math formula in grid to calculate the sum of rows’ values. First math formula calculate the sum of first three rows. Second formula calculate the sum of first three rows. it is expected the value of both sum will be updated when I enter some value into frist three rows. However first sum doesn’t work and it cannot calculate the new sum. 



I want to know whether the grid can support two or more calculations involved same cells and Is there any method to do two or more calculation involved same cells .My coding is below. Thank a lot .



 



 



<?xml version="1.0" encoding="UTF-8" ?>


100%
1.0
2.00



100%
3
4.00



100%
1
0.5



subtotal
=[[r1,1]]+[[r2,1]]+[[r3,1]]
=[[r1,2]]+[[r2,2]]+[[r3,2]]
=[[r1,3]]+[[r2,3]]+[[r3,3]]





100%
1
0.5



100%
1
0.5






subtotal
=[[r1,1]]+[[r2,1]]+[[r3,1]]+[[r4,1]]+[[r5,1]]
=[[r1,2]]+[[r2,2]]+[[r3,2]]+[[r4,2]]+[[r5,2]]
=[[r1,3]]+[[r2,3]]+[[r3,3]]+[[r4,3]]+[[r5,3]]











 


sample2.rar (146 KB)

#5

Yes, the current grid version allows only one math operation for a certain cell.

We have created a sample which demonstrates how all operations can be run when a cell is edited (in case of your xml structure).

sample.zip (1.44 KB)

#6


Thank you for your fast reply.I doubt whether it can work or not when I add more subtotal (math formula) or I added more calculation about the product of two columns (ed[=c1c2])



For example I add more columns. if i set  (ed[=c5c6]) in column 7 , can it work or not? Because we need to do a lot of calculation on product of two column. thank a lot.



 



 

#7

First ,thank you for your fast answer. However, the problem cannot be solved completely.
When I add more rows or/and columns in xml files and grids, the calculation problem come out again.
 
First problem come out  when I enter some new value in  ’ price1’ column and/or ’  quantity1’ column.
Although the subtotal of ‘price 1’column  and ‘quantity1 ‘columns can be updated in real time ,Subtotal of Amount1 column (product of  ’ price1’ column and’  quantity1’ column) cannot be updated.
 
Second problem come out when add more rows or/and columns in xml files and grids.
 
when i enter some value in’ price2’ column and’  quantity2’ column, ‘Amount2’ column (product of  ’ price2’ column and’  quantity2’ column) and  ‘Amount2’ column’s subtotal cannot be updated. But I have already set mygrid.setColTypes(‘ed,ed,ed,ed[=c1c2],ed,ed,ed[=c4c5]’);  in grid script.
 
 
Attached sample for your reference. Thank a lot.
 
sample_Updated.rar (148 KB)

#8

attached sample for your reference
sample_Updated.rar (148 KB)

#9


I have new question on mygrid.attachFooter. I find the total of "amount " column cannot be updated when i enter some value into price and quantity column.
My coding is attached.Thank a lot.





 
mygrid.attachFooter("Total,{#stat_total},{#stat_total},{#stat_total},{#stat_total},{#stat_total},{#stat_total} ") ;




 


sample_Updated.zip (169 KB)

#10


Dear Support Team ,



I have following questions.Thanks



1.) How can I export grid to excel???



2.)is there any method to differ subtotal cell from other cells and will count it while summating in my sample???



3.)I used mygrid.attachEvent method to call two fuctions respectively in doAfterXMLLoading().calculateFooterValues() is for counting the total of each column and function(stage,id,ind) is for calculating the subtotal.



 




when I edit some cells, the total of “amount” field  only updated on second editing of cells.How can I update the total of “amount field” each editing rather than second editing???Please refer to the attachment.Thanks



 



 



function doAfterXMLLoading(){



  /gets all sub_total rows/
  mygrid.forEachRow(getSub)
  
  /this event is called when cell is edited/
  //mygrid.attachEvent(“onEditCell”,calculateFooterValues);
  mygrid.attachEvent(“onEditCell”,function(stage,id,ind){
  
   if(stage == 2){
   var cell = mygrid.cells(id,3).cell;
                         var cell2 = mygrid.cells(id,6).cell;
   
    mygrid.cells(id,3).setValue(mygrid._calcSCL(cell));
                                 mygrid.cells(id,6).setValue(mygrid._calcSCL(cell2));
                          
   
    for(var i = 0; i < arr.length; i++){
     var cell = mygrid.cells(arr[i],ind).cell;
                                 
     mygrid.cells(arr[i],ind).setValue(mygrid._calcSCL(cell));
    
                                      }



   



                           for(var i = 0; i <= arr.length; i++){
     var cell2 = mygrid.cells(arr[i],ind+1).cell;
                                 
     mygrid.cells(arr[i],ind+1).setValue(mygrid._calcSCL(cell2));
    
                                      }



             
                                 
                                 



 



   }



 




mygrid.attachEvent(“onEdit”,calculateFooterValues);



   return true
  } )
  
}
 



 



 




function getSub(id){
 var class_name = mygrid.getRowAttribute(id,“class”)
 if(class_name&&(class_name.indexOf(“sub_total”)!=-1))
  arr[arr.length] = id;
}



sample_last_updated2.rar (149 KB)

#11


Your sample throughs some error so I couldn’t get it working. But the problem is in the way you set event handlers. I would not use attachEvent for calculateFooterValues, instead just call it at the end of function you set on onEditCell event while grid initialization.



As to your other questions:



1. you can save grid content as CSV (see export/import to CSV). Excel can open CSV files



2. As you probably see in email support response - you can try using treegrid and keep these values on different levels. Or you can use grid nd keep these valus in different columns



Also, please use single way of getting support - either KB or email. In other cases different people spend time with same questions from you. Thank you.

#12

Your sample throughs some error so I couldn’t get it working.

There is an issue in the code. Please, try to use:
 
for(var i = 0; i <arr.length; i++){
     var cell2 = mygrid.cells(arr[i],ind+1).cell;
     mygrid.cells(arr[i],ind+1).setValue(mygrid._calcSCL(cell2));
}

instead of

for(var i = 0; i <=arr.length; i++){

     var cell2 = mygrid.cells(arr[i],ind+1).cell;

     mygrid.cells(arr[i],ind+1).setValue(mygrid._calcSCL(cell2));

}


But probably this code is not necessary.

>> you can save grid content as CSV (see export/import to CSV). Excel can open CSV file

Grid is a client-side (JavaScript) component. So it
doesn’t provide an opportunity to export data directly to Excel.
 
But grid's API allows to serialize grid to the csv string:
 
    var csv_str = mygrid.serializeToCSV();
 
And this string can be passed to some server-side program.
 
Please, see sample of csv serialization in the documentation dhtmlxGrid/samples/clipboard/pro_clipboard.html

>> Is there any method to differ subtotal cell from other cells and will count it while summating in my sample????

You can use the function from the provided sample ( getSub ). It allows to get the row ids of subtotal cells.

And  in this case  sumColumn can be as follows:

function sumColumn(ind){
    var out = 0;
    for(var i=0;i<arr.length;i++){
            out+= parseFloat(mygrid.cells(arr[i],ind).getValue())

    }
    return out;
}
Where arr is got in getSub() function. Please, see the sample provided before.

#13


I used the following coding to calculate the total of all the rows. But I want to skip some subtotal rows. How can I skip some row of subtotal in function sumColumn() ??? i.e.    Thanks.





   



 



 



 function sumColumn(ind){
        var out = 0;
       
   
    
    for(var i=0;i<mygrid.getRowsNum();i++){
       



      //  var value=mygrid.cells2(i,ind).getValue()
     
    
       
  if (!isNaN(value)&&!value==""){
     
 
 
    out+= parseFloat(mygrid.cells2(i,ind).getValue())
           
           
        
           
   }
     }
  
  
  
  
  
  
        return out;
    }



 



 



 



xml



------------------------------------------------------------------------------------------------------



<?xml version="1.0" encoding="UTF-8" ?>


100%
1.0
2.00

1.0
2.00



100%
3
4.00

3
4.00



100%
1
0.5

1
0.5



subtotal
=[[r1,1]]+[[r2,1]]+[[r3,1]]
=[[r1,2]]+[[r2,2]]+[[r3,2]]
=[[r1,3]]+[[r2,3]]+[[r3,3]]
=[[r1,4]]+[[r2,4]]+[[r3,4]]
=[[r1,5]]+[[r2,5]]+[[r3,5]]
=[[r1,6]]+[[r2,6]]+[[r3,6]]


#14


In this case sumColumn can be as follows:





function sumColumn(ind){
    var out = 0;
    mygrid.forEachRow(function(id){   
        var cl = mygrid.getRowAttribute(id,“class”);
        if(cl&&cl.indexOf(“subtotal2”)!=-1){
            var value = mygrid.cells(id,ind);
            if (!isNaN(value)&&!value=="")
                out+= parseFloat(value);
        }
     }
    return out;
}

#15


 



thank for your answer.I have questions about export grid into CSV . can it work if i just copy following coding to my html file???Thx



 mygrid.setCSVDelimiter(",");//chnage csv delimiter
    var csvNew = mygrid.serializeToCSV();//serialize to CSV with tab as delimiter



 



 

#16

This approach allows to serialize grid into csv string. But dhtmlxgrid_nxml.js should be included.