topic Re: Use the derived data items in charts in SAS Visual Analytics

Use the derived data items in charts

paul1034 — Thu, 22 Jan 2015 14:27:56 GMT

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

Re: Use the derived data items in charts

BrunoMueller — Thu, 22 Jan 2015 15:35:28 GMT

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:

Re: Use the derived data items in charts

TejaSurapaneni — Thu, 22 Jan 2015 15:53:15 GMT

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

Re: Use the derived data items in charts

BrunoMueller — Thu, 22 Jan 2015 16:01:47 GMT

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

Re: Use the derived data items in charts

TejaSurapaneni — Thu, 22 Jan 2015 16:04:40 GMT

Can u pls send me screen shots...

Re: Use the derived data items in charts

BrunoMueller — Thu, 22 Jan 2015 16:08:42 GMT

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

Re: Use the derived data items in charts

paul1034 — Thu, 22 Jan 2015 16:09:57 GMT

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

Re: Use the derived data items in charts

TejaSurapaneni — Thu, 22 Jan 2015 16:14:19 GMT

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

Thanks,

Teja.

Re: Use the derived data items in charts

BrunoMueller — Thu, 22 Jan 2015 16:17:53 GMT

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.

Re: Use the derived data items in charts

paul1034 — Fri, 23 Jan 2015 14:04:13 GMT

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

Re: Use the derived data items in charts

BrunoMueller — Fri, 23 Jan 2015 14:18:59 GMT

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
;