Function Max by year

[DorotaR]

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

[BrunoMueller]

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-pa... by @Renato_sas for some more details on the AggregateCells operator.

[DorotaR]

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

[BrunoMueller]

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.