Number filter and equals functionality

Number filter and equals functionality

Posted by: jeff.bailey on 8 October 2018, 8:28 am EST

• 

  jeff.bailey

  • Post Options:
  • Link
    Copy
  • Jump To Answer

  Posted 8 October 2018, 8:28 am EST

  We’re using a spread control, and populate it with numeric values.

  The filter icon at the top of the column shows “Number Filters” as it should, and I can choose “Equals”. However, the matching doesn’t seem to work well when the underlying data are doubles.

  If a cell in a column has a value of “123.00” and I filter with “equals 123”, the row containing that cell isn’t shown. Same result if I filter with “equals 123.00” (which, numerically, 123 = 123.00). However, if I filter with “greater than 122.99”, or “less than 123.01”, the row is shown.

  Along the same lines, if the value in the cell is 123.01, and I filter with “equals 123.01”, the row containing that cell isn’t shown.

  This leads me to believe that the filter has a problem matching with values based on double representation. Is there a known issue along these lines?

  Thanks for any info…

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 9 October 2018, 12:47 am EST

  Hello,

  I am able to replicate this issue at my end, I am further investigating it. I will let you know about my findings soon.

  Thanks,
  
  Deepak Sharma

• 

  jeff.bailey

  • Post Options:
  • Link
    Copy

  Posted 9 October 2018, 1:34 am EST

  Thanks for the quick response - I look forward to your findings…

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 9 October 2018, 11:00 pm EST

  Hello,

  Thanks for being cooperative.
  
  The filter in this case is filtering data in data model based on the underlying values. When I enter the values in cells at run time the Text which is an unformatted data of the cell is set. In your example if I enter “123.00” in the cell and filter data based on that, it requires a Text filter and not a number filter because it will filter based on unformatted data(Text).
  
  On the other hand, if I set the cell as NumberCellType and set the value of cell as “123.00” it will filter the value with no issues.

  Please refer to the attached sample application for better understanding.

  Thanks,
  
  Deepak Sharma

  SpreadWinNumberFilterIssue.zip

• 

  jeff.bailey

  • Post Options:
  • Link
    Copy

  Posted 11 October 2018, 1:09 am EST

  I haven’t looked at your example yet, but are you saying that I have to specifically set the type of the individual cell to NumberCellType? I thought that was auto-detected, and that’s why I get “Number Filter” in the filter selection.

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 11 October 2018, 5:24 pm EST

  Hello,

  Yes, you would need to set the celltype of cell to NumberCellType so the values are identified as number and not string.

  Thanks,
  
  Deepak Sharma

• 

  jeff.bailey

  • Post Options:
  • Link
    Copy

  Posted 12 October 2018, 1:21 am EST

  I’ll try this and get back to you, but then why doesn’t “equals 123.00” work, when the cell shows “123.00”? And “greater than” and “less than” filters work on strings?

• 

  grapecity

  • Post Options:
  • Link
    Copy

  Posted 15 October 2018, 2:17 am EST

  Hello,

  You are right, when there is no celltype set the underlying data(unformatted value) is “123” used for ‘greater than’ and ‘less than’ filters. This is the same reason it shows a number filter.
  
  However when you set the celltype, the value of the cell will be as shown in the cell(with format) i.e. “123.00”.

  Please let me know if you still have doubts.

  Thanks,
  
  Deepak Sharma

• 

  nathaniel.rhodes

  • Post Options:
  • Link
    Copy

  Posted 1 October 2019, 5:20 am EST

  To follow up on Jeff’s issue, forcing the field types of each cell does not look to resolve the problem.

  I’ve tested the provided sample solution file, and the issue was still present. The cells were all set as NumberCellType, but the Equals filter was still acting as a string comparison. When showing two decimal places, the filter only works right if the user enters 123.00 (That is, exactly as the grid formats it).
  
  I believe this is only occurring with the Equals filter. Using the Not Equals filter compares those exact same cells properly, treating the cells as unformatted numbers.

  The Equals filter behaves differently than the other filters on the dropdown menu (AutoFilterMode = EnhancedContextMenu). For all the other filters, the menu displays checkboxes for whatever numeric filter you have (e.g. Number Filter > Greater Than…) This also occurs if you type a value in the Equals box and that exact number (as a string) is not in any of the cells (e.g. 123).
  
  But if you enter a value that is visible in the list of cells (e.g. 123.00), the filter is not registered as a Number Filter and that checkbox is no longer marked. Instead, it acts as if you had selected a value from the searchable list of items, between the (Select All) and (blanks) options. I’ve attached a .Zip with images of this behavior.

  This is occurring with Spread Studio 10 (v10.40.20162.0), if perhaps this was updated in a later version.

  Thanks.

  Numeric Equals Filter.zip

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 2 October 2019, 6:04 pm EST

  Hello,

  I am not able to reproduce the issue with “10.40.20174.0”. Please refer to the attached video sample. You can download the same at the following link:
  
  https://cdn.grapecity.com/SpreadStudio/installer/old/10.0.20171.0/offlineDownload_Full.zip

  if the problem still occurs them, Could you please either modify the attached application or provide your own stripped-down project so that I can try reproducing the same behavior at my end and assist you further accordingly?

  Thanks,
  
  Mohit
  
  Spread_Test_Filter.zip
  
  2019-10-03 at 12-31-07.zip

• 

  nathaniel.rhodes

  • Post Options:
  • Link
    Copy

  Posted 3 October 2019, 7:05 am EST

  In the provided video, the grid is filtered by {Equals: 123.00}, which works as expected, only showing the two rows. However, our goal is to filter by {Equals: 123}, which is numerically equivalent. All the other filter types (Not Equals, Greater Than, etc.) function as expected for numbers, but Equals only matches if you put exactly 123.00 (and not 123, 123.0, 123.000, etc.). Equals looks to be the only one that doesn’t match on number, but on formatting. So the same thing happens if you have a number >= 1000, and ShowSeparator is on and showing the thousands separator.
  
  I tested with your provided project file using v10.40.20174.0, and still saw the issue.

  A video of the issue is attached here:
  
  2019-10-03_15-36-40.zip

  In it, you can see that filtering as {Equals: 123} hides all rows, including the ones that, numerically, have that value (see Cell Value on the right). The filter is treated as a Number Filter.
  
  When the filter is set as {Equals: 123.00}, the two rows now show. The filter is no longer a Number Filter, but works like a selection from the item list.
  
  Setting the filter to {Greater Than: 123} shows that the other filter types work as expected. The two proper rows are shown, and the filter is recognized as a Number Filter.

  Sample project file used for testing:
  
  SandboxApplication.zip

  Thanks!

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 3 October 2019, 7:33 pm EST

  Hello Nathaniel,

  This is the design behavior of Spread 10 or the latest version(Spread 12). You check the same behavior with MS excel also. In Ms excel also, hide the rows containing 123.00 value when you try to filter the row with 123 value.

  Hope it clarifies.

  Thanks,
  
  Mohit

• 

  nathaniel.rhodes

  • Post Options:
  • Link
    Copy

  Posted 4 October 2019, 1:51 am EST

  Hi Mohit.
  
  I can confirm that that can possibly occur in Excel, but only if the cell is set as a Text type, not a Number. We’re trying to treat the cell as a Number. In Excel, filtering with {Equals: 123} marks both 123 and 123.00 as matching. With Spread, neither one is shown.

  If the cell type in Excel is set to Text, then the rows are not marked when using {Equals: 123}. Spread is following this behavior instead, even though it is set to use NumberType cells. So it does not appear to exactly match how Excel handles these filters. Spread’s Equals filter is acting like Excel’s Text mode, not Number.

  This was tested in the latest version of Excel through Office 365. I’ve attached a video showing this behavior:
  
  2019-10-04_10-19-38.zip

  It is difficult for our customers, since they expect the cell to always just act as a number. But if this is the intended design of the control, then will just have to work with that.

  Thanks for any help.

• 

  mohitg

  

  • Post Options:
  • Link
    Copy

  Posted 8 October 2019, 10:52 pm EST

  Hello,

  Your videos that you are applying the conditional formatting. However, if you apply filtering in the excel it will show the same behavior as in Spread. Please refer to the attached video.

  Thanks,
  
  Mohit
  
  2019-10-09 at 17-20-16.zip

• 

  nathaniel.rhodes

  • Post Options:
  • Link
    Copy

  Posted 9 October 2019, 2:21 am EST

  Mohit,
  
  I see that that is the case, I was assuming the two worked the same. I will relay this information, thanks for the clarification.