topic Re: How to create a financial aging report in SAS VA in SAS Visual Analytics

How to create a financial aging report in SAS VA

Derick — Fri, 09 Aug 2019 03:44:34 GMT

I have a dataset which contain 3 yeas records, the structure below

Company DateTime Amount

In VA, i duplicate the datetime column and make it a Month Year only column.

I want to build a aging report which show 1 years records from todate to the date 1 year before. Example August/2019 - August/2018. The record before August/2018 will sum up and show in one column. The Month Year column will change when the month is change. Example if it is September, the report will from September/2019 - Sept/2018.

Here is the table I wish to show to user.

is it possible to do in SAS VA? Please guide me. I'm using SAS VA(8.3.1) on SAS Viya(V03.04)

Thanks.

Re: How to create a financial aging report in SAS VA

FredrikE — Mon, 12 Aug 2019 10:59:21 GMT

Hi!

I think it is possible using the rank function.

First create a numeric variable containing the month (201901, 201902....)

Then use the rank function and list the top 13 months by the new numeric variable.

The rest of the months will automatically sum in the other category if you have the check box selected.

//Fredrik

Re: How to create a financial aging report in SAS VA

Derick — Fri, 16 Aug 2019 00:49:13 GMT

Hi Fredrike,

Thanks for the answer. I will give a try when my SAS server is ready back.

Re: How to create a financial aging report in SAS VA

Derick — Fri, 04 Oct 2019 08:09:22 GMT

Hi Fredrike,

I try to create a numeric variable containing month year. When I use rank function, it need me select Top Count or Top percent and then By. In the "By" drop down list, I select amount but the result is not in order for the month in current year. It show the top amount in order. How I can fix this?

Re: How to create a financial aging report in SAS VA

FredrikE — Fri, 04 Oct 2019 08:23:56 GMT

Hi!

Choose your variable containg the period from the drop down then doing the rank.

Then select the highest 13 values.

Select the new numeric variable as "by".

I tried to modify my swedish version to english 🙂

Working?

//Fredrik

Re: How to create a financial aging report in SAS VA

Derick — Fri, 04 Oct 2019 08:58:07 GMT

Still not able to do it. Here is how I assign the rank function. MonthyearInNumber is new create variable which like 201910, 201909

Re: How to create a financial aging report in SAS VA

Derick — Fri, 04 Oct 2019 09:10:24 GMT

My newly create month year data type is numeric. I try to modify the Rank function, it also not work as expected.

Re: How to create a financial aging report in SAS VA

FredrikE — Fri, 04 Oct 2019 12:30:59 GMT

Hi!

Try changing the aggregation from "SUM" to "MAX".

Otherwise you will rank on aggregated date values.

//Fredrik

Re: How to create a financial aging report in SAS VA

Derick — Thu, 10 Oct 2019 06:06:38 GMT

It's work in correct order. How to sum up all the other month in one column? Currently only show Max 12 month.

Re: How to create a financial aging report in SAS VA

FredrikE — Thu, 10 Oct 2019 09:45:23 GMT

I thought it would work by selecting then check box "All other" ?

//Fredrik