Number filter and equals functionality

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

  • 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....
  • Replied 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
  • Replied 9 October 2018, 1:34 am EST

    Thanks for the quick response - I look forward to your findings...
  • Replied 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
  • Replied 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.
  • Replied 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
  • Replied 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?
  • Replied 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
  • Replied 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
  • Replied 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
  • Replied 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!
  • Replied 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
  • Replied 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.
  • Marked as Answer

    Replied 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
  • Replied 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.
Need extra support?

Upgrade your support plan and get personal unlimited phone support with our customer engagement team

Learn More

Forum Channels