2 Replies Latest reply: Apr 22, 2012 12:27 PM by DaniePosthumus RSS

    Set analysis Fiscal YTD

      I have monthly financial periods starting from 1 March 2011 to 28 Feb 2013 - 1 to 25 (Field is called 'FinncPriod'.
      I need to do a set analyis to give me a ytd figure for the period 1 March 2011 to 29 Feb 2012 - periods 1 - 12.
      I have an inline load -
      MinPeriodFinncPriod
      11
      12
      13
      14
      15
      16
      17
      18
      19
      110
      111
      112
      1313
      1314
      1315
      1316
      1317
      1318
      1319
      1320
      1321
      1322
      1323
      1324
      13
      25
      This links to my main table 'Journals' on the 'FinncPriod' field.
      So for the 1st 12 periods (Mar 2011 - Feb 2012) the MinPeriod is always 1, the next 12 periods (Mar 2012 - Feb 2013) the MinPeriod is always 13. I am disregarding period 13 which was used for take-on balances. I created 2 variables -
      vMinPeriod = MinPeriod and
      vMaxFinncPriod = Max(FinncPriod).
      So I think the set analysis expression (with the selections Year = 2012, Month = Feb)
      sum({<MinPeriod={"=$(vMinPeriod)"},FinncPriod={"<=$(vMaxFinncPriod"} ,Drawer={'4'},Month=,Year=> } JrnAmt)
      should give me the ytd figure for Mar 2011-Feb 2012 but it doesn't work.
      If I change it to
      sum({<MinPeriod={"=$(vMinPeriod)"},FinncPriod={"<=12"} ,Drawer={'4'},Month=,Year=> } JrnAmt) I get the correct answer. How can I change the set analysis expression so that I can use the variable vMaxFinncPriod. I'm including my model - Please have a look and advise.