Group Sort on a Sum of a Sum Formula

[former_member406941]

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

[abhilash_kumar · Accepted solution]

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

[former_member406941 · Accepted solution]

Thank you!

[former_member406941]

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!

[abhilash_kumar]

Hi Rich,

You cannot group sort on such formulas.

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

-Abhilash