Is this calculation possible in VA?

IR_ANALYST_UCF — Wed, 04 Jan 2017 21:38:21 GMT

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.

Thanks for your responses.

Re: Is this calculation possible in VA?

RDHamm — Thu, 12 Jan 2017 20:59:01 GMT

Hi there. I think I have this figured out.

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

I used each of the items as a category.
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.'))
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

topic Is this calculation possible in VA? in SAS Visual Analytics

Is this calculation possible in VA?

Re: Is this calculation possible in VA?