Get average of data where 2 pivot column match

[muhammad2]

Hi Everyone

I have a looker look which uses 2 pivot dimensions. You can see below. One is Degree term ID, second is Matriculation Id. 

I want to get average of all numbers where both the pivot columns match. For example in first column

Degree term ID = c~2016!~1 and Matriculation Id = c~2016!~1 so get 3.32 and same for next column where value of both cells is c~2017!~1 so on and so forth. And average these numbers like AVG(3.32, 3.26.....)

[muhammad2]

@a_shah Please help

[a_shah]

Hi @muhammad2 , I am not able to follow the issue you are facing, can you share the way data exists in database table and depict in excel the way you want it to appear in Looker and the way it is appearing currently. This will help in understanding the issue better.

[muhammad2]

Here is the sheet.
https://docs.google.com/spreadsheets/d/1wtyD8XwQEzBFXUpGDPU9M3aVkIZ7FdEAgWy8oZ8OiaU/edit#gid=0

[muhammad2]

This is how looker data looks like

[a_shah]

Hi @muhammad2 , if I understand your requirement correctly, you want to have a cumulative average for values in "Enrollment per Learner" when values in two dimensions (Internal Degree ID and Matriculation Term ID) match. Like when the value is c~2016!~1 in both dimension then calculation must be AVG(3.32) and for value c~2017!~1 the calculation should be AVG(3.32, 3.26) = 3.29. Is my understanding correct? 

[muhammad2]

Here is again a new sheet.
Please check sheet 2 on this link.
https://docs.google.com/spreadsheets/d/1wtyD8XwQEzBFXUpGDPU9M3aVkIZ7FdEAgWy8oZ8OiaU/edit#gid=1489755...

[a_shah]

Hi @muhammad2 , Talking in terms of database as source and data being in tabular form the values for different year-terms will be in different rows. So to do calculation there may be a complex way of using lag/lead (LAG / LEAD) function in bigquery. But a simpler approach will be to write 3 sub queries where each give out the value of "Enrollment per Learning" corresponding to the year-term of outer query. e.g when the outer query will give "Enrollment per learner" value for 2020-Term2, the first sub query will join on OUTER_query.(YYYY+1)_Term2=SUBQuery.YYYY_Term2 and give value for 2021_Term2, the second will join on OUTER.(YYYY+2)_Term2=SUBQuery.YYYY_Term2 and give values for 2022_Term2 and so on. This will give values for corresponding Year-Terms in a single row and make the calculation easier.

Above will create dataset that looks like below

I updated the original dataset you gave as below, check the link.

 https://docs.google.com/spreadsheets/d/1R1lCAvtYiK4yGjF4xa2eDHhq_c6LG55rTe-8t54KEqk/edit?usp=sharing

~Ashish

[a_shah]

Hi @muhammad2 , so you want to calculate cohort for a specific Year-Term by doing average of "Enrollments per learner" for the same terms from the next 4 consecutive years. i.e. Matriculation Cohort of YYYY_T2 = AVG(YYYY_T2, (YYYY+1)_T2, (YYYY+2)_T2, (YYYY+3)_T2). so Cohort for 2020_T2 = AVG(2020_T2, 2021_T2, 2022_T2, 2023_T2). Hope I got this much right.

Now, the above sheet is a pivoted version of base data, can you please share the unpivoted tabular dataset, so that we can discuss it in terms of transforming the data using SQL to enable this calculation?

~Ashish