Visual Analytics - How to get the average of a division of sums aggrupation

[jm_pino98]

Hi everyone,

During my work I tried to get the average of a division that is created by the sum aggrupation of two variables, but everything I tried didn't work. So I want to know if it is possible to get this type of aggrupation. Here I will give you an example of the process that I'm trying to do. I'm using the SAS® Visual Analytics, Version: Stable 2023.09

So, first I have this data.

Cosecha	1	2	3	4	5
202301	0%	3%	5%	7%	10%
202302	0%	5%	6%	8%	12%
202303	0%	7%	9%	11%	13%
202304	0%	6%	7%	10%	15%
202305	0%	7%	8%	10%	13%
202306	0%	8%	10%	11%	12%
202307	0%	4%	6%	9%	11%
202308	0%	3%	5%	6%	8%
202309	0%	3%	4%	4%	6%
202310	0%	2%	3%	4%
202311	0%	1%	2%
202312	0%	5%
202401	0%

I created this table using this logic for each of the periods:

SUM(ByGroup,saldo_mora_02)/SUM(ByGroup,saldo_02)

Then is what I need. I need to get for each year the average percentage for each period, but I tried a lot of things and couldn't do it. So I want to know if it is possible to get the average per year for each of the periods. The final table has to be like this:

Cosecha	1	2	3	4	5
2023	0%	5%	6%	8%	11%
2024	0%

[KerriR_SAS]

Hi @jm_pino98  I'm assuming that the variable COSECHA is a numeric, but not a date field.  If that's true, you need to convert that into a date field so you can get the year in a date format and then have VA calculate the average percentages by year. You can do this either in the data preparation before the table is loaded into CAS or you can do that within VA using a series of calculations. (I'll include the calculations one at a time to show you the individual steps, but you could combine them to decrease the # of custom calculations.) Then you'd duplicate each of the measures (data items 1, 2, 3, 4, and 5) and change the aggregation to Average.

First, you want to get the year from COSECHA. To do that, you can create a new Calculated Item with this formula: Round(((Cosecha) / (100)),1)

After creating that calculated item (I called it "1 - Year (Category)"), change the Classification from Measure to Category.

Second, get the month from COSECHA. To do that, you create a new Calculated Item with this formula: (Cosecha / 100 - Round('1 - Year (Category)'n, 1)) * 100

Then change the classification to Category.

Third, create Cosecha in a date format using this formula (just set the day to the number 1): DateFromMDY('2 - Month (Category)'n, 1, '1 - Year (Category)'n)

Fourth, get the year as a Date from the new Cosecha date field you just created (be sure to change the format to Float so there are no commas in the displayed value): Year('3 - Cosecha (Date Format)'n)

Here's a screenshot of all those columns in the table with your original data:

Then duplicate the measures and change the aggregation to Average. (If you don't need to display the original values, you could skip the duplication and simply change the aggregation to Average.)

And finally, create your new table using Year and the measures that have the aggregation set to Average.