3 Replies Latest reply: Nov 19, 2012 9:30 AM by Geoff Blight RSS

    apparently simple set analysis failing

    Geoff Blight

      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.