BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
paul1034
Calcite | Level 5


Hi!

I want to use some of the various derived data items, like Year to Date Growth or Difference from Previous Parallel Period, in my charts and graphs.  However, I need these measures calculated for data that is compiled on a fiscal year basis, not a calendar year basis.  Any thoughts or suggestions on the best way to accomplish this in VA?

Thanks.

Paul

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

I have used the follwoing expression to build the new fiscal year, this might help you get started

IF ( Month('saleDate'n) BetweenInclusive(1, 6) )

RETURN DateFromMDY(( Month('saleDate'n) + 6 ), DayOfMonth('saleDate'n), ( Year('saleDate'n) + 0 ))

ELSE DateFromMDY(( Month('saleDate'n) - 6 ), DayOfMonth('saleDate'n), ( Year('saleDate'n) + 1 ))

View solution in original post

10 REPLIES 10
BrunoMueller
SAS Super FREQ

I suggest you build another date column, that has a proper date according to the finance year, for example your finance year begins on the 01jul2014 so you translate this into 01jan2015 and then use this new data item to build your YTD etc values.

You will need to duplicate this new data item to get fiscal year and fiscal month (you can use the month format and display it as a number) to build a report, here is an example:

TejaSurapaneni
Lapis Lazuli | Level 10

Hi, Bruno..

Thanx for u r fostest reply,

I will build new data column, but I have one question, I want it will be automatic every year...in VA

Do u have any suggestions...

Thanks,

Teja surapaneni

BrunoMueller
SAS Super FREQ

I have used the follwoing expression to build the new fiscal year, this might help you get started

IF ( Month('saleDate'n) BetweenInclusive(1, 6) )

RETURN DateFromMDY(( Month('saleDate'n) + 6 ), DayOfMonth('saleDate'n), ( Year('saleDate'n) + 0 ))

ELSE DateFromMDY(( Month('saleDate'n) - 6 ), DayOfMonth('saleDate'n), ( Year('saleDate'n) + 1 ))

TejaSurapaneni
Lapis Lazuli | Level 10

Can u pls send me screen shots...

BrunoMueller
SAS Super FREQ

However I think it is a lot easier to just copy/paste the expression in the text area, in picture above marked in red

TejaSurapaneni
Lapis Lazuli | Level 10

Thanx Bruno, for sending us screen shot, let me try, after that I will share my experience vth u, once again thanks...

Thanks,

Teja.

BrunoMueller
SAS Super FREQ

Please be aware the expression I used only works if the fiscal year starts on 01July. I suggest you add the fiscalDate column to the data that is loaded into the LASR Server.

paul1034
Calcite | Level 5

Bruno,

Thanks for your help!  In reviewing the results after implementing your calculation, I noticed some missing values.  The missing values displayed when the new fiscal date did not have a corresponding date to the sales date.  For example, August corresponds to February.  So, when a sale occurs on Aug 30th or 31st, that corresponding date in February displays as "." since the month of February does not have a 30th or 31st.  This situation is true for the months of October and April, December and June, March and September, and May and November.

My thought is another IF or CASE statement needs to be included to account for these discrepancies.  If you know of a better solution, please suggest it.  Any help is welcomed.  Thanks again!!

BrunoMueller
SAS Super FREQ

I guess the easiest way is just to assume some constant value for the day, since as you pointed out, some days might not exist in the financial month. As suggested earlier, I would create the value for the financial date outside of VA. SAS provides the INTNX function, which allows you do advance a date by some interval, see the example below:

data have;
  infile cards dlm=",";
 
input
    saleDate :
date9.
    amount :
best12.
  ;
  fiscalDate = intnx("month", saleDate, 6, "B");
  format saleDate fiscalDate date9.;
cards;
30aug2014,123
31aug2014,456
;
paul1034
Calcite | Level 5

Thanks all!  I will let you know the results when I try it later.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 10 replies
  • 1452 views
  • 6 likes
  • 3 in conversation