## Is this calculation possible in VA?

Occasional Learner
Posts: 1

# Is this calculation possible in VA?

I have this dataset below -

 FacultyID Student ID Term 1 11 2010 1 11 2011 1 11 2012 1 12 2013 1 12 2014 1 13 2012 1 13 2013 1 13 2014 1 13 2015 2 15 2011 2 15 2012 2 15 2013 2 15 2015 2 15 2016 2 15 2017 2 16 2011 2 16 2012 2 16 2013 2 16 2014 3 17 2012 3 17 2014 3 17 2016 3 17 2018 3 17 2020 3 17 2022 3 17 2024 4 18 2012 4 18 2013 4 18 2014 4 18 2015 4 18 2016 4 19 2016 4 19 2017 4 20 2016 4 21 2012 4 21 2013 4 21 2014 4 21 2015

I am interested in calculating the Average Terms a faculty is associated with a student. In the above example the average terms for fac id 1 is  3 for 2 it is 5, for 3 it is 7 and for 4 it is 3.
Formula used is Sum(Count(Terms))/Count(Student ID) for every Faculty ID.

I have other category variables associated with the student id and faculty id and hence want to calculate the item dynamically instead of aggregating the dataset prior ro bringing it into VA 7.3.

Occasional Contributor
Posts: 6

## Re: Is this calculation possible in VA?

Hi there.  I think I have this figured out.

I used your data.  These are the steps that I took:

1. I used each of the items as a category.
2. I created a new custom item that concatenated the faculty ID and the student ID
1. Fac ID - St ID = Concatenate(Format('FacultyID'n[Raw], 'BEST4.'), Format('Student ID'n[Raw], 'BEST4.'))
3. I created 3 aggregated measures from Fac ID - St ID (you could do this with one)
1. Count = Count [_ByGroup_] ('Fac ID - St ID'n)
2. Distinct = Distinct [_ByGroup_] ('Fac ID - St ID'n)
3. Avg = 'Count'n / 'Distinct'n

I was able to create the results you were looking for.

Cheers, Roger

Discussion stats