6 Replies Latest reply: May 10, 2012 12:23 AM by Matthew Green RSS

    Help needed with expression to show current financial year vs previous

    Matthew Green

      Hi all,

       

      I've got a graph that needs to show the current financial year vs previous, which I do using two expressions, the current year is

       

      NUM(Avg({$<data_measure={'Actual'},

                                    DateFinYear={$(=20 & (Num#(Mid(MaxString(DateFinYear),3,2))) & '-' & (Num#(Mid(MaxString(DateFinYear),6,2))))}>}data_value)/100,'###.##%')

       

      and the previous year is

       

      NUM(Avg({$<data_measure={'Actual'},

        DateFinYear={$(=20 & (Num#(Mid(MaxString(DateFinYear),3,2))-1) & '-' & (Num#(Mid(MaxString(DateFinYear),6,2))-1))}>}data_value)/100,'###.##%')

       

       

       

      DateFinYear is in the format of 2011-12

       

      If I create two text boxes containing the expressions:

      20 & (Num#(Mid(MaxString(DateFinYear),3,2))) & '-' & (Num#(Mid(MaxString(DateFinYear),6,2)))

      20 & (Num#(Mid(MaxString(DateFinYear),3,2))-1) & '-' & (Num#(Mid(MaxString(DateFinYear),6,2))-1)

       

      They display 2011-12 and 2010-11 when I choose 2011-12 from a list box.

       

      However when I place the formula into my section access, it fails to work.  Can anyone see why this won't work?

       

      Thanks