Who I pretended it was for: The Mayor of the City of Chicago
What I did:
I created a separate sheet for the chart data. This keeps the main data clean for the person reading it.
I used query language to create three sets of data. Two of the sets had two lists which is why I call them a set.
My first query was: “select A,B order by B desc limit 5” followed by a “=SUM(Summary!B:B)-SUM(B2:B6)”
This takes the top five departments by total paid in salaries. The departments after that are listed as OTHER and their sum is calculated by taking the total sum of all departments and subtracting the top 5 departments. This data goes into a chart that allows the mayor to get a quick overview of where the money is going.
My second query was: “=QUERY(Summary!A:B,”select A,B where A’POLICE’ AND A’FIRE’ AND A’WATER MGMT’ AND A’AVIATION’ AND A’STREETS & SAN'”)”
This takes the rest of the departments and puts them all into a list. The where A’DEPARTMENT’ makes sure the top five departments aren’t included in the list. This data goes into a smaller chart that allows the mayor go into more detail of the OTHER section of the previous chart.
The next set of data was found using two countif functions and one countifs function to count the amount of people in a salary range. This data goes into a big chart by itself. This allows the mayor to see how many people are in salary range and where maybe someone could get a pay raise.
My third query was: “select A,F order by F desc limit 5” followed by a “=SUM(Summary!F:F)-SUM(J2:J6)”
This is like the first query but this time it finds the top five departments by the number of employees. The second part does the same as the first query set’s second part does. This data also goes into a chart so the mayor can see which departments may need to downsize.
The fourth query was: “select A,F where A’POLICE’ AND A’FIRE’ AND A’WATER MGMT’ AND A’STREETS & SAN’ AND A’OEMC'”
This does essentially the same as the second query but once again with employee number rather than salaries.
What I would do differently:
I would probably use a data set with less items. This data set wasn’t good for charts or at least pretty charts.
I might make the Other chart even more subdivided.