2 Replies Latest reply: Jan 6, 2012 4:56 AM by lindsayhills RSS

    Help needed with changing script for Calendar Year to Financial Year

      Hello,

       

      I'm updating the script on our charity's fundraising QV application to differentiate between our Calendar Year (e.g. Jan-Dec 2011) and Financial Year (runs 1 July to 30 June each year).

       

      Our sheet objects use a lot of formulas like this one to compare current calendar year to last calendar year.

       

      Sum ({$<Year = {$(#vCurrentYear)}>} AMOUNT)/Sum ({$<Year = {$(#vLastYear)}>} AMOUNT)
      

       

      I'd like to be able to change these to financial year comparisons.

       

      Does anyone know what amends I'd need to make to the script on our Calendar tab (pasted below) to enable me to do that?

       

       

      Many thanks,

       

      Lindsay

       

       

       

      LET vTodaysDate = num(YearEnd(Today())); //set to the year end date as today's date
      LET vCurrentYear = Year($(vTodaysDate));
      LET vLastYear = Year($(vTodaysDate))-1;
      LET vCurrentMonth = Month($(vTodaysDate));
      LET vLastMonth = Month($(vTodaysDate))-1;
      Let vPTD = $(vTodaysDate)-1;
      
      
      Calendar:
      Load * Inline [
      FinancialDATE, Start, End
      7/1/2001,       7/1/2000,     7/31/2000
      8/1/2001,       8/1/2000,    8/31/2000
      9/1/2001,       9/1/2000,    9/30/2000
      10/1/2001,      10/1/2000,    10/31/2000
      11/1/2001,      11/1/2000,   11/30/2000
      12/1/2001,      12/1/2000,   12/31/2000
      1/1/2001,       1/1/2001,     1/31/2001
      2/1/2001,       2/1/2001,    2/29/2001
      3/1/2001,       3/1/2001,     3/31/2001
      4/1/2001,       4/1/2001,     4/30/2001
      5/1/2001,       5/1/2001,      5/31/2001
      6/1/2001,       6/1/2001,     6/30/2001
      
      
      7/1/2002,       7/1/2001,     7/31/2001
      8/1/2002,       8/1/2001,    8/31/2001
      9/1/2002,       9/1/2001,    9/30/2001
      10/1/2002,      10/1/2001,    10/31/2001
      11/1/2002,      11/1/2001,   11/30/2001
      12/1/2002,      12/1/2001,   12/31/2001
      1/1/2002,       1/1/2002,     1/31/2002
      2/1/2002,       2/1/2002,    2/29/2002
      3/1/2002,       3/1/2002,     3/31/2002
      4/1/2002,       4/1/2002,     4/30/2002
      5/1/2002,       5/1/2002,      5/31/2002
      6/1/2002,       6/1/2002,     6/30/2002
      
      
      7/1/2003,       7/1/2002,     7/31/2002
      8/1/2003,       8/1/2002,    8/31/2002
      9/1/2003,       9/1/2002,    9/30/2002
      10/1/2003,      10/1/2002,    10/31/2002
      11/1/2003,      11/1/2002,   11/30/2002
      12/1/2003,      12/1/2002,   12/31/2002
      1/1/2003,       1/1/2003,     1/31/2003
      2/1/2003,       2/1/2003,    2/29/2003
      3/1/2003,       3/1/2003,     3/31/2003
      4/1/2003,       4/1/2003,     4/30/2003
      5/1/2003,       5/1/2003,      5/31/2003
      6/1/2003,       6/1/2003,     6/30/2003
      
      
      7/1/2004,       7/1/2003,     7/31/2003
      8/1/2004,       8/1/2003,    8/31/2003
      9/1/2004,       9/1/2003,    9/30/2003
      10/1/2004,      10/1/2003,    10/31/2003
      11/1/2004,      11/1/2003,   11/30/2003
      12/1/2004,      12/1/2003,   12/31/2003
      1/1/2004,       1/1/2004,     1/31/2004
      2/1/2004,       2/1/2004,    2/29/2004
      3/1/2004,       3/1/2004,     3/31/2004
      4/1/2004,       4/1/2004,     4/30/2004
      5/1/2004,       5/1/2004,      5/31/2004
      6/1/2004,       6/1/2004,     6/30/2004
      
      
      7/1/2005,       7/1/2004,     7/31/2004
      8/1/2005,       8/1/2004,    8/31/2004
      9/1/2005,       9/1/2004,    9/30/2004
      10/1/2005,      10/1/2004,    10/31/2004
      11/1/2005,      11/1/2004,   11/30/2004
      12/1/2005,      12/1/2004,   12/31/2004
      1/1/2005,       1/1/2005,     1/31/2005
      2/1/2005,       2/1/2005,    2/29/2005
      3/1/2005,       3/1/2005,     3/31/2005
      4/1/2005,       4/1/2005,     4/30/2005
      5/1/2005,       5/1/2005,      5/31/2005
      6/1/2005,       6/1/2005,     6/30/2005
      
      
      7/1/2006,       7/1/2005,     7/31/2005
      8/1/2006,       8/1/2005,    8/31/2005
      9/1/2006,       9/1/2005,    9/30/2005
      10/1/2006,      10/1/2005,    10/31/2005
      11/1/2006,      11/1/2005,   11/30/2005
      12/1/2006,      12/1/2005,   12/31/2005
      1/1/2006,       1/1/2006,     1/31/2006
      2/1/2006,       2/1/2006,    2/29/2006
      3/1/2006,       3/1/2006,     3/31/2006
      4/1/2006,       4/1/2006,     4/30/2006
      5/1/2006,       5/1/2006,      5/31/2006
      6/1/2006,       6/1/2006,     6/30/2006
      
      
      7/1/2007,       7/1/2006,     7/31/2006
      8/1/2007,       8/1/2006,    8/31/2006
      9/1/2007,       9/1/2006,    9/30/2006
      10/1/2007,      10/1/2006,    10/31/2006
      11/1/2007,      11/1/2006,   11/30/2006
      12/1/2007,      12/1/2006,   12/31/2006
      1/1/2007,       1/1/2007,     1/31/2007
      2/1/2007,       2/1/2007,    2/29/2007
      3/1/2007,       3/1/2007,     3/31/2007
      4/1/2007,       4/1/2007,     4/30/2007
      5/1/2007,       5/1/2007,      5/31/2007
      6/1/2007,       6/1/2007,     6/30/2007
      
      
      7/1/2008,       7/1/2007,     7/31/2007
      8/1/2008,       8/1/2007,    8/31/2007
      9/1/2008,       9/1/2007,    9/30/2007
      10/1/2008,      10/1/2007,    10/31/2007
      11/1/2008,      11/1/2007,   11/30/2007
      12/1/2008,      12/1/2007,   12/31/2007
      1/1/2008,       1/1/2008,     1/31/2008
      2/1/2008,       2/1/2008,    2/29/2008
      3/1/2008,       3/1/2008,     3/31/2008
      4/1/2008,       4/1/2008,     4/30/2008
      5/1/2008,       5/1/2008,      5/31/2008
      6/1/2008,       6/1/2008,     6/30/2008
      
      
      7/1/2009,       7/1/2008,     7/31/2008
      8/1/2009,       8/1/2008,    8/31/2008
      9/1/2009,       9/1/2008,    9/30/2008
      10/1/2009,      10/1/2008,    10/31/2008
      11/1/2009,      11/1/2008,   11/30/2008
      12/1/2009,      12/1/2008,   12/31/2008
      1/1/2009,       1/1/2009,     1/31/2009
      2/1/2009,       2/1/2009,    2/29/2009
      3/1/2009,       3/1/2009,     3/31/2009
      4/1/2009,       4/1/2009,     4/30/2009
      5/1/2009,       5/1/2009,      5/31/2009
      6/1/2009,       6/1/2009,     6/30/2009
      
      
      7/1/2010,       7/1/2009,     7/31/2009
      8/1/2010,       8/1/2009,    8/31/2009
      9/1/2010,       9/1/2009,    9/30/2009
      10/1/2010,      10/1/2009,    10/31/2009
      11/1/2010,      11/1/2009,   11/30/2009
      12/1/2010,      12/1/2009,   12/31/2009
      1/1/2010,       1/1/2010,     1/31/2010
      2/1/2010,       2/1/2010,    2/29/2010
      3/1/2010,       3/1/2010,     3/31/2010
      4/1/2010,       4/1/2010,     4/30/2010
      5/1/2010,       5/1/2010,      5/31/2010
      6/1/2010,       6/1/2010,     6/30/2010
      
      
      7/1/2011,       7/1/2010,     7/31/2010
      8/1/2011,       8/1/2010,    8/31/2010
      9/1/2011,       9/1/2010,    9/30/2010
      10/1/2011,       10/1/2010,    10/31/2010
      11/1/2011,       11/1/2010,   11/30/2010
      12/1/2011,       12/1/2010,   12/31/2010
      1/1/2011,       1/1/2011,     1/31/2011
      2/1/2011,       2/1/2011,    2/29/2011
      3/1/2011,       3/1/2011,    3/31/2011
      4/1/2011,       4/1/2011,     4/30/2011
      5/1/2011,       5/1/2011,     5/31/2011
      6/1/2011,       6/1/2011,    6/30/2011
      
      
      7/1/2012,       7/1/2011,     7/31/2011
      8/1/2012,       8/1/2011,    8/31/2011
      9/1/2012,       9/1/2011,    9/30/2011
      10/1/2012,       10/1/2011,    10/31/2011
      11/1/2012,       11/1/2011,   11/30/2011
      12/1/2012,       12/1/2011,   12/31/2011
      1/1/2012,       1/1/2012,     1/31/2012
      2/1/2012,       2/1/2012,    2/29/2012
      3/1/2012,       3/1/2012,    3/31/2012
      4/1/2012,       4/1/2012,     4/30/2012
      5/1/2012,       5/1/2012,     5/31/2012
      6/1/2012,       6/1/2012,    6/30/2012
      
      
      7/1/2013,       7/1/2012,     7/31/2012
      8/1/2013,       8/1/2012,    8/31/2012
      9/1/2013,       9/1/2012,    9/30/2012
      10/1/2013,       10/1/2012,    10/31/2012
      11/1/2013,       11/1/2012,   11/30/2012
      12/1/2013,       12/1/2012,   12/31/2012
      1/1/2013,       1/1/2013,     1/31/2013
      2/1/2013,       2/1/2013,    2/29/2013
      3/1/2013,       3/1/2013,    3/31/2013
      4/1/2013,       4/1/2013,     4/30/2013
      5/1/2013,       5/1/2013,     5/31/2013
      6/1/2013,       6/1/2013,    6/30/2013
      ];
      
      
      
      
      LEFT JOIN(Calendar)
      INTERVALMATCH(DATE)
      Load
      Start, //StartParameter for intervalmatch()
      End //EndParameter for intervalmatch()
      RESIDENT Calendar;
      
      
      
      
      TempCalendar:
      //LOAD
      //          *,
      //          CalendarYear & '-' & CalendarQuarter as CalendarYearQtr,
      //          if(CalendarYear = Year($(vTodaysDate)), 1, 0) as CY_Flag,
      //          if(CalendarYear = (Year($(vTodaysDate))-1), 1, 0) as LY_Flag, 
      //          if(CalendarMonth = Month($(vTodaysDate)), 1, 0) as CM_Flag,
      //          if(CalendarMonth = (Month($(vTodaysDate))-1), 1, 0) as LM_Flag 
      //          ;
      LOAD 
                DATE,
                Year(DATE) as CalendarYear,
                Month(DATE) as CalendarMonth,
                Date(Monthstart(DATE), 'MMM-YYYY') as CalendarYearMonth,
                if(Ceil(Month(DATE)/3)>0,'Qtr' & Ceil(Month(DATE)/3)) as CalendarQuarter,
                Year(DATE) as Year,
                Month(DATE) as Month,
                Date(Monthstart(DATE), 'MMM-YYYY') as YearMonth,
                if(Ceil(Month(DATE)/3)>0,'Qtr' & Ceil(Month(DATE)/3)) as Quarter
      RESIDENT FINANCE;
      
      
      TempFinancialCalendar:
      LOAD 
                FinancialDATE,
                Year(FinancialDATE) as FinancialYear,
                Month(FinancialDATE) as FinancialMonth,
                Date(Monthstart(FinancialDATE), 'MMM-YYYY') as FinancialYearMonth,
                if(Ceil(Month(FinancialDATE)/3)>0,'Qtr' & Ceil(Month(FinancialDATE)/3)) as FinancialQuarter
      RESIDENT Calendar;
      
      
      
      
      LEFT JOIN (Calendar)
      LOAD *
      RESIDENT TempCalendar;
      
      
      LEFT JOIN (Calendar)
      LOAD *
      RESIDENT TempFinancialCalendar;
      
      
      DROP TABLE TempCalendar; 
      DROP TABLE TempFinancialCalendar;