apparently simple set analysis failing

[#1]

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.

[jonathandienst]

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

[swuehl · Accepted solution]

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))

[#4]

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