BookmarkSubscribeRSS Feed
jm_pino98
Calcite | Level 5

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.

 

Cosecha12345
2023010%3%5%7%10%
2023020%5%6%8%12%
2023030%7%9%11%13%
2023040%6%7%10%15%
2023050%7%8%10%13%
2023060%8%10%11%12%
2023070%4%6%9%11%
2023080%3%5%6%8%
2023090%3%4%4%6%
2023100%2%3%4% 
2023110%1%2%  
2023120%5%   
2024010%    

 

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:

 

Cosecha12345
20230%5%6%8%11%
20240%    

 

1 REPLY 1
KerriR_SAS
SAS Employee

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:

Calculating_Averages_in_VA.PNG

 

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.

 

Calculating_Averages_in_VA (Data items).PNG

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 1 reply
  • 473 views
  • 1 like
  • 2 in conversation