Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello all...
The following piece of set analysis expression does not produce a value...
=if ([Academic Year] = ComparatorYear
,Sum(Total_Target_Sep)
,Sum({1<[Academic Year]={$(=Only(ComparatorYear))}>} Total_Enrolments))
Where [Academic Year] (from my data set) and ComparatorYear (selection from listbox) contain text values ('2010/11', '2011/12', '2012/13' etc).
Now, if I substitute 1 and zero for the two sums, the output confirms that the academic and comparators either match or don’t depending on selections. If I just substitute the second sum with zero, the output is either the sum of targets or zero as expected. But when I include the second sum, neither sums are produced.
Curiously, this next example works spot on – the difference being that in this case the academic_year_text and comparatorYear2 are in the form 2010, 2011, 2012 etc.
=if(Academic_year_text = ComparatorYear2
, sum(Total_Target_Sep)
, Sum({1<Academic_year_text={$(=only(ComparatorYear2))}>} Total_Enrolments))
Can anyone see anything obviously wrong or tell me that I'm doing it the wrong way?
Cheers,
Geoff.
Try enclosing the set modifier list values in single quotes to prevent from evaluating the math expression 2012 / 13 etc.:
=if ([Academic Year] = ComparatorYear
,Sum(Total_Target_Sep)
,Sum({1<[Academic Year]={'$(=Only(ComparatorYear))'}>} Total_Enrolments))
Hi
Only(ComparatorYear) will return a null if more than one ComparatorYear value is possible. I think this is what you need:
=If([Academic Year] = ComparatorYear
,Sum(Total_Target_Sep)
,Sum({1<[Academic Year] = P(ComparatorYear)}>} Total_Enrolments))
Assumes that AcademicYear and ComparatorYear in the same format.
Hope that helps
Jonathan
Try enclosing the set modifier list values in single quotes to prevent from evaluating the math expression 2012 / 13 etc.:
=if ([Academic Year] = ComparatorYear
,Sum(Total_Target_Sep)
,Sum({1<[Academic Year]={'$(=Only(ComparatorYear))'}>} Total_Enrolments))
Thats sorted it thank you. Not sure when I would have realised the expression was trying to resolve 2012 divided by 2013.
Thanks for all your replies