Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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)
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
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