Filtering a calculated measure does not work as expected.

Posted by: trail-hacker on 3 August 2017, 3:57 pm EST

  • Posted 3 August 2017, 3:57 pm EST

    Whenever we try to filter on a calculated measure, the results are never predictable. We've been able to reproduce the problem pretty consistently and can't find a suitable work around. Basically, we would expect that when applying a measure filter against a calculated measure would exclude all values that don't fall within that range. This concept holds true for non-calculated measures, but not for calculated measures.

    Here are the steps to reproduce the problem.

    1.) Unzip the files into the [MyDocs]\GrapeCity\ActiveAnalysis\DataSources\*.* directory.

    2.) Run the WinViewer sample that ships with ActiveAnalysis setup.

    3.) Click on 'Data' menu item and choose 'Connect to DataSource...'

    4.) Select the new 'Northwind (Sales)' connection.

    4.) Drop [Orders].[OrderID] on Row shelf

    5.) Drop [Product].[Product Name] attribute to the right of [Orders].[OrderID] on Row shelf

    6.) Drop the following measures on the Data shelf

    - [Measures].[UnitPrice]

    - [Measures].[Quantity]

    - [Measures].[SalesAmount]

    - [Measures].[Discount]

    - [Measures].[Margin]

    7.) Move [Measures].[MeasureNames] on Column shelf to make the display look readable.

    -- NOTE: Your display should be similar to the First.jpg screenshot

    8.) Now, apply a Measure filter to only show members where [Measures].[Margin] is between $100 - $500

    - Display the [Measures].[Margin] dropdown

    - Select 'Show quick filter'

    - Set the low value to 100

    - Set the high value to 500

    -- NOTE: Your display should be similar to the Second.jpg screenshot

    I would expect that the display would display all records where the Margin value is between $100 and $500. :P

    BONUS: You can see that this works as expected if you apply a quick filter on 'UnitPrice' (10 - 20), or any other NON-CALCULATED measure.
  • Replied 3 August 2017, 3:57 pm EST

    Thanks Andrey!

    Also, I recently discovered that sorting is broken on this calculated measure as well. I assume it has something to do with the same underlying problem as the filtering mechanism.

    If you wish, I can create another post with modified steps and screenshots. (?) Otherwise, simply include this little fact in the case please. :)

    Thanks again..
  • Replied 3 August 2017, 3:57 pm EST

    <h2>Trail Hacker,</h2>Thank you for detailed description and sample. We've opened case 151492 to address this issue and will investigate it shortly.

    Thank you,
    Andrey T.

  • Replied 3 August 2017, 3:57 pm EST

    Yes, if you can please post steps for sorting issue in this topic. It will help in investigating/testing process.

    Thank you,
    Andrey T.
  • Replied 3 August 2017, 3:57 pm EST

    Is there any update for case 151492? Thank you for any additional information.

  • Replied 3 August 2017, 3:57 pm EST

    Trail Hacker,

    I would like to tell you that Case 151492 has been resolved in an interim build of ActiveAnalysis. If you wish then I can send you a link to download the interim build to test this issue at your end.

  • Replied 3 August 2017, 3:57 pm EST

    That's great news!

    We can wait on the official release. We have a temporary work around for now using calculated fields in the sql string, and it is working well for our client. The next release will allow us to move the calculation back out to the schema file.

    Thanks for the update.
Need extra support?

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

Learn More

Forum Channels