2 Replies Latest reply: Sep 3, 2010 2:32 PM by Erico Aleixo RSS

    mapping fiscal quarters and years to master calendar

      I'm encountering an issue mapping a fiscal year to my master calendar. I'm trying to use an inline mapping load for simplicity, also because I don't know a better way. I need to map fiscal year and fiscal quarter to my master calendar. for fiscal calendar my map is:

      Month, FiscalQuarter
      1, Q3
      2, Q3
      3, Q3
      4, Q4... and so on ];

      with applymap('FiscalQuarter_Map', Month(TempDate)) as FiscalQuarter in my MasterCalendar load script. It works nicely.

      FiscalYear turned out to be a bit more complicated because I tried to map it using a field I created, MonthYear. In the MasterCalendar load script MonthYear is defined as: Date(monthstart(TempDate), 'MMM-YY') AS MonthYear So my map is:

      MonthYear, FiscalYear
      Aug-06, 2007
      Sep-06, 2007

      etc etc etc
      Jul-10, 2010 ];

      I tried applymap('FiscalYear_Map', Date(monthstart(TempDate), 'MMM-YY')) as FiscalYear ... However, then the list box FiscalYear returns the same thing as my MonthYear field instead of 2007 2008 2009 2010 which I would expect. So it is returning Aug-06 Sep-06 etc. If I try 'YYYY' as my last parameter the list box then contains 2006 five times, 2007 twelve times etc.

      The second parameter in the applymap function seems to be my problem. I would like to put the field MonthYear in that parameter, because that is what I am trying to map on. However, I can't do that because MonthYear is a field that I am defining in the same load script. So, I tried to put the definition of MonthYear in that second parameter instead just like I did for my FiscalQuarter map, but it did not work because it created an entry for FiscalYear for every month as explained above.

      I hope I explained that ok. If anyone has another strategy or something to put in that second parameter that they think might make this work i would love to hear it! Thanks a lot.


        • mapping fiscal quarters and years to master calendar

          This example can be helpful!!!




          Load Distinct


          'Q' & Ceil(Month(FechaPedido)/3) as Quarter,

          Dual('Q' & Ceil(Month(FechaPedido)/3) & '-' & Year(FechaPedido), Year(FechaPedido) & Ceil(Month(FechaPedido)/3)) as QtrYear,

          Year(FechaPedido) as Ano,

          Month(FechaPedido) as Mes,

          Day(FechaPedido) as Dia,

          Dual(Year(FechaPedido) & '-' & Month(FechaPedido), Year(FechaPedido) & Month(FechaPedido)) as MesAno,

          Year(FechaPedido) & Num(Month(FechaPedido),'00') as Period,

          Year(AddMonths(FechaPedido,-1)) & Num(Month(AddMonths(FechaPedido,-1)),'00') as PeriodAnt,

          AddMonths(FechaPedido,-1) as FechaAnt,

          Num(Month(FechaPedido),'00') as MesNum

          Resident Pedidos

          Order By FechaPedido Asc;