topic Re: Function Max by year in SAS Visual Analytics

Function Max by year

DorotaR — Mon, 05 Jul 2021 11:10:57 GMT

Dear,

I need help in Visual Studio to calculated max and min.

I have data:

for one of category

Year Premium (sum by group) Sum insured (sum by group) Rate(sum premium/ sum sum insured)

2018 1000 1 000 000 0,1%

2019 10 000 2 000 000 1%

2020 15 000 3 000 000 1,5%

I would like to take max of rate for category: 1,5% for 2020 year. How can I do that? Which function?

Best regards

Dorota

Re: Function Max by year

BrunoMueller — Tue, 06 Jul 2021 06:57:03 GMT

Assuming a few things since there is no information about this:

You are using Visual Analytics 8.5 or higher
You want to do this in a VA report

Took the data from sashelp.cars for the example.

List table looks like this:

The "engine / cylinders" is calculated like this:

Sum [_ByGroup_] ('enginesize'n) / Sum [_ByGroup_] ('cylinders'n)

The "max of pct" is calculated like this:

AggregateCells(_Max_, 'engine / cylinders'n, default, CellIndex(
start, 0), CellIndex(end, 0))

The AggregateCells operator works on the data that is displayed by the object. Have a look at this post https://communities.sas.com/t5/SAS-Communities-Library/SAS-Visual-Analytics-Advanced-Calculations-part-1-of-4/ta-p/538509 by @Renato_sas for some more details on the AggregateCells operator.

Re: Function Max by year

DorotaR — Thu, 08 Jul 2021 08:15:57 GMT

Hi,

Thank you for your reply. Function Aggregate Cells work good, but I need fixed my max for year. And I have problem because

function AggregateTable does not supported aggregate data. Is it possible to do that?

I want max=0,63% but without showing year in column, only one value.

I am using VA 8.5.1

Best Regards

Dorota

Re: Function Max by year

BrunoMueller — Fri, 09 Jul 2021 08:13:53 GMT

In this case I suggest you use an Working with Aggregated Data Sources

This will allow you to create a new datasource in your VA report with the aggregated data. Please note that the data element "max of pct" from the previous example will not be available in the aggregated data source, but can easily be derived using

Max [_ForAll_] ('engine / cylinders'n)

If needed the new aggregated data source can be joined back to the original data.