Discussions

 View Only

Formula Query Using Summary Field

  • 1.  Formula Query Using Summary Field

    Posted 19 days ago
    Edited by Matt Jones 19 days ago
    Hello!

    Having an issue using a summary field to rank customers based on revenue. On our Customers table, the Total $ Shipped is a summary field, and I'm wondering if it's causing the problem I'm seeing in my formula query.

    Here's my formula:

    var text formulaquery = "{236.GT.'" & [Total $ Shipped] & "'}AND{236.XEX.'0'}";

    If([Total $ Shipped]=0,null,Size(GetRecords($formulaquery))+1)

    When I look at a report that sorts by this ranking, it has no number 1 record, and two number 2 records. The two records that are tied for number 2 do not match. They are off by almost 10,000. It then ranks 4, 5, 6, and 7 accurately (except for skipping number 3) and then has two number 8 and two number 10- which also are not equal to each other. I've read that it's best to use user input fields for formula queries because there's additional background calculations going in to summary fields, but it wasn't really made clear that it wouldn't work with summary fields. Any way to get this to work?

    ------------------------------
    Matt Jones
    ------------------------------