SAS Visual Analytics MIN and MAX

[WilliamARosario]

Hello,

I have the table below in memory, i need to generate a report of a single line, which has the min and max, as an example, i need to generate this in the VA designer.

* min = minimum sum of month (in case the months here are January and February, get the lowest vlr and qtde)
* max = maximum sum of month (in case the months here are January and February, get the highest vlr and qtde)

Tks for all

[Arjen]

In Report Designer you can create new columns with aggregated values that will do exactly what you need.

- In Report Designer select the Data tab and choose your data source

- Click on the icon with the down-arrow (directly below the data source, to the right of the trash can icon)

- Select "New Aggregated Measure..."

- In the dialog, type a name for the measure, e.g. "MinVlr"

- Expand "Aggregated (simple)" in the Operators section on the left side of the dialog

- Drag the Min function onto "result" in the center of the dialog

- Double click the text "Measure" after "Min _ByGroup_" in the center of the dialog

- Type the name of the column you want the minimum of (e.g. vlr) and hit Enter

- Click the "OK" button

You'll notice that the new field will be added to the "Aggregate Measure" section on the left side of the screen. You can now use it in a report.

[WilliamARosario]

Thanks for the answer,

but what I need is to create a measure grouped only by the anomes field

[WilliamARosario]

I need to dynamically generate this intermediate step in the SAS VA.

[Arjen]

Ok, now I understand what you need to do. I don't think you can get this done in the report designer of VA, but you can do this in Prepare data with two Data-queries.

The first query generates the intermediate results (sum of valor and qtde grouped by anomes), the second dataquery uses this intermediate table to generate the final values. If you run the second query, the first query will be automatically run and the output table can be used in the report designer.

To do this, open Data Preparation and select "New dataquery". Select your table and drop it in the middle of the screen. Select the columns you need and change it to look like this (my datasource is BOOK1):

I named this query "IntermediateValues".

After this, create a new Data Query and use "IntermediateValues" as the source for this query. This would look something like this:

If you save and run this query, it will create a dataset with one record (specify the name in the "Outputs" tab on the right side of the screen). This will contains all the values you need. 

The query can be scheduled to run every x minutes or whatever to keep the output table updated.

BTW: You can set the labels of the output columns in the "Output Columns" tab at the bottom of the screen.

BTW2: To get the fields in the order you have specified in your attachment, check the aggregate functions in the order MAX,AVG,MIN. This will result in MIN,AVG,MAX