topic Re: Period calculations in VA using Financial Year in SAS Visual Analytics

Period calculations in VA using Financial Year

dmcloughlin — Thu, 06 Mar 2014 14:53:20 GMT

I am currently on client site in UK where the company operates in financial years which are between April and March - ie we are currently in FY13/14. I am currently summarising datasets in EG using a character field which I have created in the format using intnx (FY2013_14) - I am trying to introduce VA, but would require the ability to use the period calculations on financial years. It appears that the period calculations require date format - does anyone have any suggestions on how to achieve this? Someone has suggested a custom format - but not sure how that would work.

eg - I am looking to do something like this, where "%Movement" uses the period calculations:

Year	Expenditure	% Movement
FY2008_09	£100,000
FY2009_10	£110,000	10.0%
FY2010_11	£120,000	9.1%

Thanks,

Damian

Re: Period calculations in VA using Financial Year

revi_sas — Thu, 06 Mar 2014 19:32:56 GMT

Have you looked at this SAS(R) Visual Analytics 6.3: User's Guide Creating Derived Items in Exploration, It does require the use of date format.

Re: Period calculations in VA using Financial Year

dmcloughlin — Thu, 06 Mar 2014 21:51:20 GMT

Yes I have seen the documentation, and used the functionality with standard years and months - but the issue is that I need to define the financial year as a date field - I was wondering if there is a workaround that anyone has used or come across.

Re: Period calculations in VA using Financial Year

PeterConroy — Sun, 09 Mar 2014 01:10:58 GMT

Damian

What you could do is create a separate custom measures in your report to accumulate the expenditure for each FY2008_09, FY2009_10, FY2010_11 by filtering the each year in the custom measure. For example:

IF Year = FY2008_09

THEN Expense (Measure)

ELSE 0.

Then aggregate (SUM) the these measures TOT_ FY2008_09, TOT_FY2009_10 and TOT_FY2010_11. All you need next is a aggravated calculation for the percentage change for each year you are comparing. for example:

IF (TOT_ FY2008_09 = 0) AND (TOT_FY2009_10 = 0)

THEN 0

ELSE IF (TOT_ FY2008_09 = 0) AND (TOT_FY2009_10 > 0)

THEN 1 /* 100% increase */

ELSE IF (TOT_ FY2008_09 > 0) AND (TOT_FY2009_10 = 0)

THEN -1 /* -100% increase */

ELSE (TOT_FY2009_10 - TOT_ FY2008_09 > 0) / TOT_ FY2008_09 > 0.

*******

TOT_ FY2008_09 = 0 and TOT_FY2009_10 = 0 then aggravated calculation measure = 0

TOT_ FY2008_09 = 0 and TOT_FY2009_10 > 0 then aggravated calculation measure = 1

TOT_ FY2008_09 > 0 and TOT_FY2009_10 = 0 then aggravated calculation measure = -1

TOT_ FY2008_09 = 100 and TOT_FY2009_10 = 110 then aggravated calculation measure = 0.1

(110 - 100) / 100 = 0.1 or 10%

If you prefer whole numbers e.g. 1 = 100 then change 1 to 100, -1 to -100 and calculation (TOT_FY2009_10 - TOT_ FY2008_09 > 0) / TOT_ FY2008_09 * 100 so you final display has no percentage symbol.

*******

Then format the aggravated calculation measure to % with the required decimals.

I have used this in list objects with square bullets that change colour depending on the percentage change, for example green = change less than 0, yellow less than plus 10% change and Red = over 10% change.

I am interested if there are any other solutions.

Peter Conroy

Re: Period calculations in VA using Financial Year

dmcloughlin — Mon, 10 Mar 2014 12:41:25 GMT

Thanks for this Peter - I had considered this - and this is useful thanks!

I think that its safe enough to say that it is not possible to use the period calculations with a financial year at present - perhaps this is something that SAS can look at developing for future iterations.

Re: Period calculations in VA using Financial Year

MichelleHomes — Tue, 11 Mar 2014 05:39:07 GMT

FYI, I raised a feature request via SAS Technical Support in September 2013 to create a date hierarchy for financial years.

What I requested...

Tech Support Issue #7611085989 Ability to create a Date Hierarchy for financial years?

The new feature of being able to "Create a Date Hierarchy" in SAS Visual Analytics Explorer is fantastic. Are their plans in the future to enhance the creation step so that a financial year hierarchy can be created rather than calendar year? If not, can you please add it as a feature request please.

Also it would be nice to have this feature, 'Create Date Hierarchy' in Visual Analytics Designer.

As a "financial year" can vary from country to country I also suggest to have it configurable as part of the installation/configuration where a financial year setting can be made or perhaps within the VA interfaces general settings.

What do other VA users think?

Re: Period calculations in VA using Financial Year

PeterWijers — Tue, 11 Mar 2014 08:58:23 GMT

Michelle,

I do agree that the functionality technically is perfect on calendar dates but not for financial dates. (We use 4-4-5 week system)

Generally I am not using the functionality because it does not match our fin.rep.period system, and that should be changed.

I can only commit to your requests :

- Create option to define fin.periods.

- Make period functionality related to Fin.Per. dates.

- Hierarchy of Fin.Period in Explorer would be nice.

Greetings Peter

Re: Period calculations in VA using Financial Year

MichelleHomes — Tue, 11 Mar 2014 09:03:48 GMT

Thanks for your feedback Peter. Interestingly, I am aware of other VA users in Australia who would like to do 4-4-5 analysis too.

I'm looking forward to see what is demoed at SAS Global Forum at the end of the month to see what is coming in 6.4...

Re: Period calculations in VA using Financial Year

MichelleHomes — Thu, 12 Jun 2014 07:23:26 GMT

Hi There,

I was wondering if there are any plans in a future release to implement financial year hierarchies or to have a time-based aggregated measure such as Year over Year Growth/Year to Date etc to be based on financial years?

Thanks,

Michelle

Re: Period calculations in VA using Financial Year

PeterWijers — Thu, 12 Jun 2014 16:54:09 GMT

Michelle,

Had a talk with Mary Clarke and Sheila Crosby, and also mentioned this problem again.

Lets see what's there in 7.1

greetings

Re: Period calculations in VA using Financial Year

AnnaBrown — Tue, 03 Feb 2015 16:53:31 GMT

Hello, here is a related thread on this matter:

Anna

Re: Period calculations in VA using Financial Year

cmwillis02 — Thu, 05 Feb 2015 16:49:17 GMT

We encountered the same problem last year as we are on a 445 Financial reporting schedule. Instead of trying to figure out how to use native functionality, or force formulas to make it work, we just added a new table to our production database that had every applicable date as well as its corresponding 445 Year, Month Week etc...

This prevents you from having to duplicate your logic when you want to use a fiscal calendar. Instead you simply reference your translation table for all dates across your organization. This is not the most elegant situation, but it was simple to do and has been very functional.

Re: Period calculations in VA using Financial Year

PeterWijers — Sat, 07 Feb 2015 11:28:31 GMT

Hi cmwillis,

this is the same solution we are using, simple and effective for now.

greetings