Skip to Content
0

Group Sort on a Sum of a Sum Formula

Nov 09, 2017 at 03:29 PM

49

avatar image

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

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

3 Answers

Best Answer
Abhilash Kumar
Nov 10, 2017 at 09:18 PM
1

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

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you!

0
Rich Fobes Nov 10, 2017 at 02:34 PM
0

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!

Share
10 |10000 characters needed characters left characters exceeded
Abhilash Kumar
Nov 10, 2017 at 11:37 AM
0

Hi Rich,

You cannot group sort on such formulas.

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

-Abhilash

Share
10 |10000 characters needed characters left characters exceeded