Help needed with changing script for Calendar Year... - Qlik Community

Report Inappropriate Content · ‎2011-12-21

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;

pover · ‎2011-12-21

Rather than changing the script, you'll need to change the variable vCurrentYear and vLastYear to max(FinancialYear) and max(FinancialYear-1).

Regards, Karl

Report Inappropriate Content · ‎2012-01-06

Thank you so much. I think that's done it!

Best,

Lindsay

Help needed with changing script for Calendar Year to Financial Year