BookmarkSubscribeRSS Feed
dmcloughlin
Fluorite | Level 6

Hi

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:

%

YearExpenditure% Movement
FY2008_09£100,000
FY2009_10£110,00010.0%
FY2010_11£120,0009.1%

Thanks,

Damian

12 REPLIES 12
revi_sas
SAS Employee

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.

dmcloughlin
Fluorite | Level 6

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.

PeterConroy
Calcite | Level 5

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

dmcloughlin
Fluorite | Level 6

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.

MichelleHomes
Meteorite | Level 14

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?

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
PeterWijers
Lapis Lazuli | Level 10

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

MichelleHomes
Meteorite | Level 14

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...

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
MichelleHomes
Meteorite | Level 14

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

//Contact me to learn how Metacoda software can help keep your SAS platform secure - https://www.metacoda.com
PeterWijers
Lapis Lazuli | Level 10

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

AnnaBrown
Community Manager

Hello, here is a related thread on this matter:

Anna

cmwillis02
Fluorite | Level 6

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.

PeterWijers
Lapis Lazuli | Level 10

Hi cmwillis,

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

greetings

SAS Innovate 2025: Register Now

Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 12 replies
  • 7256 views
  • 8 likes
  • 7 in conversation