Dynamic Formulas

Tags: #<Tag:0x00007f8b1d7a49f0>
#1

Hello,

I am trying to get a summary row at the top of the table that will update based on the filtered rows.

I attempted to use the columnSummary feature, but it did not seem to add up correctly.
I ended up adding a top row with the =SUM(D2:D7)…this works great until the list is filtered.
I know I can add a function that will set the formula, if I knew the number of rows that are included in the filter.

Or is there a better way to do this?

I would also be interested in a summary row based on the region and hiding the summary row(s) from the filter dropdowns.

Here is the example: http://jsfiddle.net/3bk2put7/

Thanks
Travis

#2

Thank you for sharing. I will look into this on Monday.

#3

Good Morning,

Have you had an opportunity to look into this?

Really liking the power of Handsontables.

Thank you for any assistance.

Travis

#4

Sorry for keeping you waiting. We are still in alpha stage of development with the Formula Support plugin so many approaches are still buggy.

I got a bit lost when I saw that the Formulas in the row index 0 are counting totals from rows with #VALUE! I am sure that this is not intended. May I change it to something else for the testing purposes?

#5

Understandable.

This is a forecasting page for our distributors. We want to see is different between the forecast and the actual sales for each distributor.

The formulas in row index 0 are the SUM of all Forecast Data, SUM of all Actual Sales Data, and the different between the SUM of all Forecast Data and the SUM of all Actual Sales Data.

When I trying doing a SUM of the Differences, I got an error. So I changed it to be the difference between the SUMs.

Yes…that is a stripped down version of the production code for your testing and troubleshooting.

#6

I have added some data as without it it was really hard for me to track progress on the changes http://jsfiddle.net/kr1of3a2/

When I change anything in the column Forecast or Actual I get updated SUM in row 0 and a change in the Difference column. That is a decent way to handle this calculations.
When it comes to summary calculations they are a bit buggy. We have decided to change their logic as well (add them as a new table, not a new row) but there hasn’t been enough time to push the changes on the production.

#7

My original question was around the filtering. Is it possible to capture the filtering event and then update row 0 formula to have the correct number of cells based on the filter number of rows.

The example has 6 row plus the 1 Total row. One of the formula in row 0 is =SUM(D2:D7). When the user filters by region and removed the northeast, 3 rows are displayed. The formula is still looking for 6 cells and then throws a #REF error, because there are only 3 rows in the table.

If I can capture the filtering event, then I can update the formula via code. If that is possible, I would need to know how many rows are currently displayed after filtering is completed?
Also can row 0 be removed from the filtering choices (e.g. always be displayed)?

Thanks
Travis

#8

You can use afterFilter hook to pass a new formula via setDataAtCell method. The countRows method also can be used to get an actual number of rows in the table.

#9

Is it possible to hide values within the filter_by_value section of the dropdown?

I don’t want the user unselecting the TOTALs column.

#10

I am sorry but we do not have any API that will support that yet.

#11