Log In
Register
Home
New Member
November 30, 2024
Offline
I have data from students at a University. I have worksheets for each University, then have combined them with a power query into one worksheet and added a pivot table. I want to see how many students a year are at each University and how they are funded. Conceptually it is straight forward, but I think I haven't set up my data properly.
Right now I have start and end dates in two columns. So if they start in 2023 and end in 2025 they are students for 3 years. I can use the start date and the pivot table nicely group by year, but that only gets me the start year. I don't know how to add the logic within a pivot table to do the math to give me the three years from 2023-2025 between the start and end date.
One way I thought of doing this is to have an entry per year for every student (so the same entry with years 2023, 2024, 2025). Is this my best option or can someone offer another idea?
It seems to successfully group by Grant number even if they are in different columns, so this is nice.
Thanks for any help!
Moderators
January 31, 2022
Online
Without seeing the data and the desired output it's difficult to comment on alternative solutions. Though it indeed makes sense to have one entry for each year a student has been enrolled.
VIP
Trusted Members
December 7, 2016
Offline
Hello,
You can do the calculation in Power Query if you only are interested in the year diffence between end and start date. Extract start and end year in new columns, and then add a custom column where you subtract start year from end year. Remove unwanted extra columns and save.
Br,
Anders
New Member
November 30, 2024
Offline
Thanks. I am having trouble loading a file, so I've copied a very simple example. Columns 8 & 9 are start and end dates.
DATA:
| Allen | Tom | Y | Proj1 | UnivB | Bob | PhD | Oct-19 | Jul-23 | N | Grant2 | ||
| Bay | Uri | N | Proj 2 | UnivB | Bob | UG | Dec-22 | Dec-28 | N | Grant1 | Grant2 | Grant3 |
Pivot Table:
| Column Labels | |||
| 2019 | 2022 | Grand Total | |
| Count of Student first name | 1 | 1 | 2 |
The columns on the pivot table are using Year of start date. (I don't care about months, quarters etc). So it is showing correctly that one student started in 2019, another in 2022.
However, I want to count each year they are in school, so 2019, 2020, 2021, 2022, 2023 for Tom and so on for Uri.
Is there an easy way to fill the years between the start and end date and duplicate the data? I can think of complicated ways, but so far the simplest way seems to be to manually copy the data for each person for each year they are in school, changing the end year.
Thanks for the support
Fran
1 Guest(s)
