SQL update
The progress so far on understanding SQL is unfortunately very early days. Last week however, I caught up with an experienced Business Analyst, who has opened my eyes, as it were, to the world of spreadsheet dashboards. I spent a fair few hours this week scrawling through Internet lessons on how to present data from spreadsheets in a dynamic way, and hopefully I've now arrived somewhere quite interesting. This takes advantage of the QUERY function, practically the same as learning pure SQL, so this is my present diversion.
Google Spreadsheets
The advantage of using Google is two fold. 1) It is far easier to build something I can share with people at work (Google Ireland). After spending time learning this stuff, I want it easily accessible and familiar for everyone, and believe it or not, some people in our office simply don't run Excel on their laptops. Moreover, it just seems un-Googley to not use Spreadsheets! 2) I can keep it updated and current. I did start building in Excel, but the kind of data I want to show need to be updated on a daily basis. Sending out an Excel file every day is impractical, so I'm building my dashboard in Google.
A case for Excel
Just before we leave the point, I have to point out that Excel does seem to be significantly more powerful, so once I start working with much bigger sets of data in more complex ways, I wouldn't be surprised if I swapped, particularly with Office 365 looking much more Internet friendly.
The Dashboard - the good bit!
The way I see it, a dashboard needs to turn a whole shed load of dump data into a report - some kind of presentation that makes it useful. For starters, you need a database:
I made the mock above about how a fictitious Alice and Bob played a game to win Coins and Stars. It's really simple data. The idea on the dashboard is that you can choose either Alice or Bob, and choose how many Coins they won. The result is how many Stars they got for each time they won this many Coins. While this seems completely useless, you will appreciate that being able to query tables of data like this in a Google Spreadsheet could be kind of handy.
The Query
Just getting the hang of putting this query together took a while. The one I've settled for (for now) is:
"=Query(Database!A1:D200, "select A, B, C, D where C = "&F3&" and B contains """&C3&""" ",1)"
Entered into Dashboard!C9, this returns a table based on the entries above. Now to break it down:
a) =Query(data, query, headers): This is the function. Pretty simple stuff. Enter a 1 for headers when starting out.
b) Database!A1:D200: This is my source, which references the first sheet. You can reference other spreadsheets too using importRange("SPREADSHEET KEY","RANGE"
c) "select A, B, C, D: I started by selecting all the columns. In the same file you use A, B, C etc. but if referring to another file, use Col1, Col2, Col3 etc.
d) where C = "&F3&": Notation for referring to numbers should be in quotes and &s.
e) and B contains """&C3&""": Notation for strings has two more quotes. Not sure why but it took me a lot of faffing to clock this. Also, note the and as opposed to adding another where.
You can find a complete list of syntax on the Query Language Reference page here.
The Chart
So easy, but oh so cool. Just make a chart as you normally would, and as you change the value the chart will move. This will impress your boss, I promise you. For good formatting, you might move your entry fields and charts around so that you have a nice clean page where the user doesn't have to see data at all, but just charts.
Next Step
The plan is to go and apply some of this strategy to relevant data we collect at work, to show some useful things. I've also got some learning to do on how to use dates, hence why in my spreadsheet the dates don't work.
Links/References
No comments:
Post a Comment