topic How to Calculate value from last FY last date to selected date from filter. in SAS Viya

How to Calculate value from last FY last date to selected date from filter.

shubha_da — Fri, 24 Nov 2023 12:34:12 GMT

Dear All,

I need to calculate (in VIYA 4.0) the value from last FY last date to selected date from report.
Example: If user select 10Nov2023 from filter, then the value should calculate the difference and %difference from 31st Mar2023. If user select 05Jan2023 then value should calculate from 31Mar22 to 5th Jan2023.

Thanks,
S Bakshi

Re: How to Calculate value from last FY last date to selected date from filter.

mkeintz — Fri, 24 Nov 2023 21:48:37 GMT

@shubha_da wrote:
Dear All,

I need to calculate (in VIYA 4.0) the value from last FY last date to selected date from report.
Example: If user select 10Nov2023 from filter, then the value should calculate the difference and %difference from 31st Mar2023. If user select 05Jan2023 then value should calculate from 31Mar22 to 5th Jan2023.

Thanks,
S Bakshi

What do mean by "from filter"? Is that supposed to be the fiscal year end date?
And what units do you want for "calculate the difference"? Number of days?
And for "% difference", percent of what?

Re: How to Calculate value from last FY last date to selected date from filter.

shubha_da — Sat, 25 Nov 2023 02:59:57 GMT

Hi,
Thanks for your reply.

I have 2 columns. Date and Amount.
Date Amount
25/02/2022 100
31/03/2022 50
12/05/2022 100
10/09/2022 150
02/03/2023 75
31/03/2023 100
05/10/2023 200
13/11/2023 150

Now, Date filter has given in the dashborad and in a list/tabular table I want values differences as well as % differences of selected date from last fiscal year last date.
If user select 10/09/2022 then value difference should come 100 (150-50) and percentage as 300% (calculating from 31/03/2022 to 10/09/2022). Calculations should be always from last fiscal year last date.

Re: How to Calculate value from last FY last date to selected date from filter.

mkeintz — Sat, 25 Nov 2023 03:15:10 GMT

@shubha_da wrote:
...
Calculations should be always from last fiscal year last date.

How do you know what date is the end of the fiscal year? If your data is for corporations, each company can have a different fiscal year end date.

Re: How to Calculate value from last FY last date to selected date from filter.

shubha_da — Sat, 25 Nov 2023 07:10:53 GMT

This requirement is for one of the Banking client in India and for this client fiscal year starts with 1st April to 31st Mar.

Re: How to Calculate value from last FY last date to selected date from filter.

SASKiwi — Sun, 26 Nov 2023 20:09:39 GMT

With SAS VA (I'm assuming you are using SAS Visual Analytics on Viya 4) you have two choices. Either you can calculate your financial year when preparing the data to be loaded into memory or you can create a VA formula that will derive the FY once the data is loaded into memory. Personally I find it easier to do this when preparing the data:

data Want;
  input trandate ddmmyy10. Amount;
  FY_Date = intnx ('YEAR.4', trandate, 0, 'END');
  format trandate FY_Date date9.;
datalines;
25/02/2022 100
31/03/2022 50
12/05/2022 100
10/09/2022 150
02/03/2023 75
31/03/2023 100
05/10/2023 200
13/11/2023 150
;
run;