Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Rather than changing the script, you'll need to change the variable vCurrentYear and
vLastYear to max(
FinancialYear) and max(
FinancialYear-1).
Regards, Karl
Thank you so much. I think that's done it!
Best,
Lindsay