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
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
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!
Hi Rich,
You cannot group sort on such formulas.
Could you post the logic used in one of the month formulas?
-Abhilash