Skip to Content

Group Sort on a Sum of a Sum Formula

I'm trying to Group Sort on a sum formula but the formula is not appearing as an option in the Group Sort Expert dropdown. The formula I want to sort by is a 'sum-of-a-sum' formula. This is it:

Sum ({@JanM}, {@AdjName})

+Sum ({@FebM}, {@AdjName})

+Sum ({@MarchM}, {@AdjName})

+Sum ({@AprilM}, {@AdjName})

+Sum ({@MayM}, {@AdjName})

+Sum ({@JuneM}, {@AdjName})

+Sum ({@JulyM}, {@AdjName})

+Sum ({@AugM}, {@AdjName})

+Sum ({@SeptM}, {@AdjName})

+Sum ({@OctM}, {@AdjName})

+Sum ({@NovM}, {@AdjName})

+Sum ({@DecM}, {@AdjName})

The formula itself works fine, CR just wont let me group sort by it for some reason. These other formulas do appear as options in the Group Sort Expert dropdown: Sum {@JanM}, Sum of {@FebM}, Sum of {@MarchM}, etc....(all of the months), just NOT the total Sum formula which is a sum of all the monthly formulas. Help?!

Thank you!!

Rich

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Nov 10, 2017 at 09:18 PM

    Hi Rich,

    Try this please:

    1. Create a formula called @GroupSum with this code:

    {@JanM} + {@FebM} + {@MarchM} + {@AprilM} + {@MayM} + {@JuneM} + {@JulyM} + {@AugM} + {@SeptM} + {@OctM} + {@NovM} + {@DecM}

    2. Insert a Summary on this formula field and place it on the Group Header/Footer

    3. Use the Group Sort Expert to Sort of this Summary field.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 10, 2017 at 02:34 PM

    Thanks so much Abhilash.

    The monthly formulas look like this. Example {@JanM}:

    If NumericText ({@Monthly Count Jan})

    Then ToNumber ({@Monthly Count Jan})

    Else 0

    And the {@Monthly Count Jan} formula looks like this:

    if {PTS_1_18_13_10_09.Entry Date/Time} >= DateTime (2017,01,01)

    and {PTS_1_18_13_10_09.Entry Date/Time} < DateTime (2017,02,01)

    then "1"

    else "0"

    Thank you!

    Add comment
    10|10000 characters needed characters exceeded

  • Nov 10, 2017 at 11:37 AM

    Hi Rich,

    You cannot group sort on such formulas.

    Could you post the logic used in one of the month formulas?

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded