Conditional Max Scale

[NickHoff]

I'm attempting to use a conditional on the max scale of a chart, which will either display the max of a reference line or the current data range.

By it self

SUM({$<CalendarYear =, CalendarMonthName =,Program -= {'Prevention'},AddedOn={">=$(=addmonths(max(AddedOn),-12))  <=$(=monthend(max(AddedOn),-1))"}>}DISTINCT  MonthlyTargetValue)+25

Will give me the correct range for the reference line

By it self

MAX(SUM({$<CalendarYear =, CalendarMonthName =, Program -= {'Prevention'},AddedOn={">=$(=addmonths(max(Today()),-12))  <=$(=monthend(max(Today()),-1))"}>}CardiologistscheduledNN))+25)

Will give me the correct range for the expression of the chart.

When I try to put it together with the following conditional it doesn't work...

=IF(SUM({$<CalendarYear =, CalendarMonthName =,Program -= {'Prevention'},AddedOn={">=$(=addmonths(max(AddedOn),-12))  <=$(=monthend(max(AddedOn),-1))"}>}DISTINCT  MonthlyTargetValue)

>=

MAX(SUM({$<CalendarYear =, CalendarMonthName =, Program -= {'Prevention'},AddedOn={">=$(=addmonths(max(Today()),-12))  <=$(=monthend(max(Today()),-1))"}>}CardiologistscheduledNN)),

SUM({$<CalendarYear =, CalendarMonthName =,Program -= {'Prevention'},AddedOn={">=$(=addmonths(max(AddedOn),-12))  <=$(=monthend(max(AddedOn),-1))"}>}DISTINCT  MonthlyTargetValue)+25,

MAX(SUM({$<CalendarYear =, CalendarMonthName =, Program -= {'Prevention'},AddedOn={">=$(=addmonths(max(Today()),-12))  <=$(=monthend(max(Today()),-1))"}>}CardiologistscheduledNN))+25)

[swuehl · Accepted solution]

Your second expression should not work MAX(SUM( Field)) is not a correct expression, using an aggregation inside another aggregation.

Try

RangeMax(

SUM({$<CalendarYear =, CalendarMonthName =,Program -= {'Prevention'},AddedOn={">=$(=addmonths(max(AddedOn),-12))  <=$(=monthend(max(AddedOn),-1))"}>}DISTINCT  MonthlyTargetValue)

,

SUM({$<CalendarYear =, CalendarMonthName =, Program -= {'Prevention'},AddedOn={">=$(=addmonths(max(Today()),-12))  <=$(=monthend(max(Today()),-1))"}>}CardiologistscheduledNN)

)

+25