10 ms·

The pivot table, the spreadsheet's most powerful tool (2020)

342 points by airstrike 3y ago

23664429-4 3y ago
Bzhf
ipython 3y ago
It's too bad that the pivot table is a poor approximation of a true multidimensional spreadsheet, for example Lotus Improv: https://instadeq.com/blog/posts/no-code-history-lotus-improv-spreadsheets-done-right-1991/ https://instadeq.com/blog/posts/no-code-history-lotus-improv...
- qsort 3y ago
  Which is itself the poor man's groupby. At some point you've just got to admit you have the wrong abstraction. Strong Zalgo vibes.
  - airstrike 3y ago
    Or at some point you realize we need some new abstraction ;-)
  - jasode 3y ago
    >Which is itself the poor man's groupby. SQL GROUP BY creates summarized horizontal rows. Instead, pivot tables are more analogous to crosstab queries which creates summarized vertical columns. It "pivots" data groupings by rotating from horizontal to vertical. The older versions of SQL dialects that didn't have the newer cross tab syntax required convoluted CASE syntax to "simulate" pivot tables which didn't really work that well since one had to know ahead of time -- all the unique values -- to put in each CASE condition branch.
    - qsort 3y ago
      If you are grouping over them, they shouldn't be columns in the first place, the original SQL is "right". Rows and columns aren't symmetric! Reshaping data should be a presentation-time decision, not a query-time decision. You have a dataset, a relation in the algebraic sense, and you are choosing to display it in some way: as a table, as a pivoted table, as a pie-chart... Conflating the two is a consequence of spreadsheets having an unbeatable UX but a terrible data model that lets you treat rows as columns and vice-versa.
      - fifilura 3y ago
        There are real use cases for pivoting data in SQL not only for presentation. Tables can be too "long". You need data points to be on the same row if you want to do arithmetic operations on on different types of values. For example k + 3.14* number_of_chimneys - 0.13 * age - 1.34*neigbourhood_criminality as house_price
      - toyg 3y ago
        > Reshaping data should be a presentation-time decision When you potentially billions or trillions of data points, it isn't. Rows and columns have limits. You need some hard logic for true multidimensional data at scale.
        quantified 3y ago
        For business analyst purposes, the reshape should be a display-time activity. Finance models may start with billions but tend to present a manageable quantity to the user. The chain of calculations back to any stored data is the real key, along with what your interactivity needs for multi-user update are.
      - tomnipotent 3y ago
        > should be a presentation-time decision, not a query-time decision Agree, but sometimes you just need to shove the results of a SQL query into an Excel file and you don't want to get fancy. You're either 1) overwriting Sheet B and then using a pivot table in Sheet A to get the final presentation, 2) pivoting in the code/program executing the query before writing to Excel or 3) pivoting in SQL and skipping the code and Excel pivot table altogether. I run into this a lot with data used for financial modeling, or financial reporting that heavily relies on using dates/categories as column/row headers.
  - contravariant 3y ago
    Goupby may be the higher abstraction, but it's not necessarily better. Dimensional models are basically modules (generalised linear spaces), a groupby can do the same things but doesn't really give much useful structure to work with (at best the result is ordet independent, most of the time). This is also why sums and counts tend to be more useful than averages.
  - quantified 3y ago
    They're different. Group by only gets you so far. Actual modeling of business scenarios deals with a lot of irregularity and the specialized spreadsheet-like tools were mostly much much better at expressing this.
- vondur 3y ago
  The linked article specifically mentions Lotus Improv as the app that had this functionality in it. Interesting how Steve Jobs was able to get Lotus to make it a NeXT exclusive app initially.
- RGamma 3y ago
  Sounds a lot like what you can do with tabular model (PowerPivot) and DAX (measures) in Excel now.
- Brian_K_White 3y ago
  The article says Improv is where pivot tables started.
- dannyobrien 3y ago
  I remember going to the UK launch of Lotus Improv as a newbie journalist. It was really notable how both how flashy and professional it was compared to other products (in retrospect, I'm presuming that was Jobs' influence). Nonetheless, I think they really struggled to explain pivot tables, and why, in itself, that feature was sufficient to move to a new application and a new hardware platform.
  - fractallyte 3y ago
    Improv included an excellent animated presentation which explained its features perfectly. The problem was to get new users to sit their asses down and actually watch it. In hindsight, this was obviously a UX failure - but I equally blame users who lacked any attention span.
- fiddlerwoaroof 3y ago
  I believe this is a sort of descendant of Improv: https://quantrix.com/products/quantrix-modeler/ https://quantrix.com/products/quantrix-modeler/
  - steve1977 3y ago
    It is, and like Improv, has its roots in NeXSTSTEP. http://www.kevra.org/TheBestOfNext/ThirdPartyProducts/ThirdPartySoftware/PersonalProductivity/SpreadSheet-Database/Quantrix/Quantrix.html http://www.kevra.org/TheBestOfNext/ThirdPartyProducts/ThirdP... There used to be a version of Quantrix Modeler that was affordable for „home users“, it’s been quite a while though.
- ghaff 3y ago
  One of the "crimes" (he types hyperbolically) of Microsoft Office is that is basically sucked all the air out of the room for anything else in the non-graphical artist office productivity area that wasn't Office or a pretty direct knock-off. The spreadsheet model is a good example (even if Excel is probably the best thing in Microsoft Office. But it also means that if you can't make Word do a good enough job for desktop publishing you generally have to go to InDesign which is probably way overkill if yoiu're not a publishing professional.
  - znpy 3y ago
    To be honest i saw a teacher in high school working on his own textbook)the second revision of an already published textbook) in word and while it had the classic wysiwyg experience, it was typographically okay, almost ready to be printed.
    - ghaff 3y ago
      Word, or even something like Google Docs which lacks some features in areas like Section numbering, isn't terrible. I published a book using Google Docs and basically decided anything it couldn't do I didn't need or could handle manually. But, if I were actually come up with a wish list for a low-end publishing platform it would probably look a bit different than Word.
  - firecall 3y ago
    A widely shared article from a few years back claimed that Excel was the world's most popular design tool. I googled - I couldnt find the article :-) I think it was in the Guardian or some such.
    - tnecniv 3y ago
      I’d believe it if you count every random plot people make as “design.” It blows my mind how much people do in excel. On one hand it’s pretty cool how much it can do but you end up with these monster spreadsheets that should really be their own program of some sorts. One example I think about a lot (because I use the end product a lot) is Fangraphs’ ZiPs model that predicts baseball stats for upcoming seasons. It’s 20 years old and, from what I’ve been told, a massive Excel book with some VB. The creator was a stats major but they didn’t do much programming in his curriculum so Excel was the option he was most comfortable / productive with. Yet, he’s doing a massive analysis over every player in the league using decades of historical data. The thought of doing that in Excel makes my stomach churn but if it works for him then I guess it’s good enough!
- II2II 3y ago
  I have not used spreadsheets very often since the mid-1990's. One of the reasons: I was excited by the potential of Improv, but disappointed when I realized that it had no future. Little did I realize that pivot tables were a different take on the concept! (The other reason for abandoning spreadsheets was performance. I forget how good/bad Improv was in this respect, but I doubt that I would have stuck with spreadsheets since the data sets I was dealing with weren't really appropriate for them.)
- huhtenberg 3y ago
  Half of the QZ article is literally about Salas and Improv.
- re5i5tor 3y ago
  On NeXT 2 years before Windows
- getravi 3y ago
  Never knew Improv existed. There are tools that are similar to the vision of Improv. I use Anaplan at work everyday and it is exactly what a modern cloud based SaaS version of Improv would feel like. It is a multi billion dollar company and it worked because it did not go after the spreadsheet space but played along nicely with it. The Improv article concludes "the key strategy mistake was to try to market Improv to the existing spreadsheet market. Instead, if the product were marketed to a segment where the more structured model was a ‘feature’ not a ‘bug’ would have given Lotus the time to learn and improve and refine the model to a point where it would have satisfied the larger market as well." and Anaplan seems to not have made this mistake. They have carved out a niche in the EPM (Enterprise Performance Management) market.
  - quantified 3y ago
    Improv's model was poor though, it was based on cell positions and it was easy to double-count. Basically was bad at various semantic aspects. I worked on a small conpetitor back in the day and we competed on modelling, there were a number of others too. I've also worked on Anaplan and and its modeling is also much better, so please don't lower it to any version of Improv! It isn't, or at least wasn't a little while back, good at cross-metric ("line item" to them") calculations and presentations, but still easier to get something complex correct. The real mistake of Improv was that it wasn't 1-2-3 so Lotus didn't know how to narket it or sell it.
- kagakuninja 3y ago
  You may want to read about DataPivot, developed by Brio Technology during the same period as Lotus. Brio had a patent on the pivot data aggregation algorithm, I'm not sure how Lotus's pivot table worked... https://en.wikipedia.org/wiki/Brio_Technology https://en.wikipedia.org/wiki/Brio_Technology
- somat 3y ago
  I use postgres in the role of a better* spreadsheet. Huge asterisk: Better is a very subjective, Ad-hoc data entry is terrible by comparison, but bulk operations are much nicer. The real reason for the change was that I was starting to loathe the spreadsheet data model. I am not fond of how easy it is screw up data in the big bag of cells data model that spreadsheets offer. The core feature I really wanted is row level security, for rows to to stick together.
  - ElectricalUnion 3y ago
    The missing part "better spreadsheet" for me is some standard, simplified reactive evaluation declaration syntax. I'm probably wrong, of course. Oracle has a pretty crazy feature where it handles a query like a temporary spreadsheet where you can enact reactive queries upon [1], that I haven't found in other SQL DBs. I know you can do that using window functions + recursive CTEs but under this "reactive evaluation of a sub-query" use case they tend to get ugly and incomprehensible real fast. [1] https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm https://www.oracle.com/webfolder/technetwork/tutorials/obe/d...
klysm 3y ago
Just let me write SQL please
- DanAtC 3y ago
  Dynamically pivoting SQL is a pain in most dialects
  - breadwinner 3y ago
    You can write a normal SELECT * FROM FOO query and use a tool like Pebble Reports to do the pivoting: http://www.pebblereports.com/videos/crosstab/ http://www.pebblereports.com/videos/crosstab/
- ChrisClark 3y ago
  You can in Google Sheets. Well, close enough at least. Use the QUERY function.
- whalesalad 3y ago
  duckdb will let you do both! https://duckdb.org/docs/sql/statements/pivot.html https://duckdb.org/docs/sql/statements/pivot.html
- yunohn 3y ago
  IME pivoting on SQL results is a very common usecase too.
- chasd00 3y ago
  ignorance is bliss but you should learn to work all your tools well not just rely on one.
- baq 3y ago
  common use case: get a metric truckload of data from a column store binned per minute (or 5), give the giant sheet to users, users use pivot tables to create hourly/daily/weekly reports as they see fit. (not all users are dumb it turns out)
- jabroni_salad 3y ago
strongpigeon 3y ago
I remember in Google Ads, we’d build all these cool looking dashboards but whenever we did user research it was pretty apparent that all they wanted was their data in a pivot table…
- c4mpute 3y ago
  All these cool-looking dashboards are just too inflexible. You cannot add your own aggragates beyond trivialities. You cannot just "color that one value that bugs you". You cannot just generate a readable report plus some explanatory text. Spreadsheet export + pivot table gives you all that. Doable for any moderately competent office drone without a round-trip through some endless backlog-spec-sprint-program-test-respec-sprint-... loop
  - c4mpute 3y ago
    To be somewhat constructive: What you rather should have done is not create more elaborate dashboards. What imho the world needs is an easy way to use a spreadsheet tool to generate and publish a dashboard. A "make web dashboard" button right next to the print button. With auto-updates when input data changes of course.
    - airstrike 3y ago
      Yup. That's what I want to build. Thank you for saying that -- I feel like it really validates my feelings hah
      - fiddlerwoaroof 3y ago
        The problem is always this project turns into “let’s build excel or tableau” and the customers that care usually already use one or the other.
        airstrike 3y ago
        That's fair, but fortunately I'm not planning on doing either. (Well, I am still implementing ~all of Excel's formulas for compatibility, but not the the UI/UX...) People don't really consume data, they read documents. I think that's (part of) the vision these projects lack.
gadders 3y ago
I was working at Lotus when Improv came out. From what I remember, it was popular but as you started adding dimensions memory use blew up. This was in the early days of Windows and 4mb of memory.
- kagakuninja 3y ago
  And I was working at Brio Technology in the late 90s, they had a similar product called DataPivot, first sold in 1991. https://en.wikipedia.org/wiki/Brio_Technology https://en.wikipedia.org/wiki/Brio_Technology I don't remember Brio's pivot tables "blowing up" per-se, but I suppose computers had a lot more memory by the time I joined. They used a patented algorithm to create the pivot structure by aggregating a result-set from an SQL query.
asah 3y ago
nice! AI is about to end this - shortly, we'll be able to ask for the answer directly, in zero clicks.
- Brian_K_White 3y ago
  and have no confidence in it
- rchaud 3y ago
  Perhaps we could create a set of logical prompts to get there even faster. We could call it a structured query language!
  - mxuribe 3y ago
    Or better yet: conversation query language (CQL)! ...Though the risk exists for verbal name collision. ;-)
- Bishonen88 3y ago
  Dunno, seems to me that asking (writing in this case) is not final solution. In fact, in most cases, typing down a question will take longer than looking in a well created table. This chat-like back-and-forth, will take forever to understand some dataset properly. One question answered will give birth to 5 additional questions and so on.
- airstrike 3y ago
  this is unfairly downvoted, IMHO -- i doubt it will take "zero clicks" -- we're more likely replacing clicks with prompts -- but LLMs can definitely help bridge the gap between data wrangling and presentation. replies to this comment seem to overvalue the challenges without acknowledging that those may eventually be overcome.
  - seektable 3y ago
    search-driven analytics is not really a new thing and products in this space were before LLM-era. This kind of interface can be useful for some categories of users but it is not a game-changer - prompts cannot replace Excel and its pivot tables, and in fact typing prompts may be even more complicated for users than good old 'clicks'.
thrdbndndn 3y ago
this probably is more about specific implementations than pivot table in general, but I often find it's "lacking" when wanting to do some more advanced things with it. To name a few: The way how the "values" are generated is very limited in both Excel and Google Sheets (in different ways). The way how filter/sorting works with pivot table isn't the most straightforward or flexible. the "UI" elements (headers, styles, etc.) is very hard to control. I often find myself creating a pivot table, and copy it somewhere else, and manually fix bunch of stuff -- which kinda defeats the point of it (since I can no longer dynamically update it). Disclaimer: I'm by no means a spreadsheet expert so I may just miss something.
- airstrike 3y ago
  Agreed, current implementations are totally brittle and far from "feature complete" relative to what an experienced / power user would expect coming in It actually bothers me so much I've decided to write my own spreadsheet engine. This is one of the pain points I want to fix
- seektable 3y ago
  You may try these online pivot tables https://www.seektable.com https://www.seektable.com where you can re-order rows/columns simply with a click on the header, apply filters via simple input where you can specify which items to keep or exclude.
- conductr 3y ago
  I’m pretty close to a spreadsheet expert, I categorize pivot tables as just quick and dirty adhoc/disposable data visualization and never would use it as something for presentation or where styling mattered. I usually use it to quickly find unexpected values in the underlying data columns. Although with spill formulas like =unique() I’m using it less and less for this. I find often people wanting more out of it, are really asking for Power Query. Which is there, just a lot of people are intimidated by. (Maybe not HN, but general population)
Qem 3y ago
The second most powerful is probably the Solver, that few programming languages (like prolog) include equivalent functionality out of the box. That's one thing that consistently gets me crawling back from Python/Pharo to Calc.
tqi 3y ago
Pivot tables powered by SSAS cubes may be the best self serve analytics tool I've ever seen (where "best" is measured by how much users actually use it). The ability to meet business users where they are is huge for actual adoption, especially compared to something like Looker (which is hot garbage for other reasons as well). Plus, 9 times out of 10 people want to apply additional lightweight transformations/calculations to the data, so nothing beats being in Excel already. Too bad OSX support is non existent and writing MDX is a pain in the fucking ass.
- doppenhe 3y ago
  this makes me happy , I was the PM for that feature :)
- davio 3y ago
  Back in the mid 2000s, our killer app was an example app from MSDN magazine. It basically embedded Excel in a simple web page and we could use the pivot tables against SSAS cubes. We did a little web work for permissions and to save views, but probably had less than a total week of dev work.
- hermitcrab 3y ago
  Have you tried Easy Data Transform? It is a lightweight ETL tool for doing data transformations (such as pivot) on Excel, CSV and various other file formats. Runs natively on Windows and Mac.
  - tqi 3y ago
    The ETL part is not really the problem business users seem to have, but rather needing the ability to use pre-built measures / dimensions natively in Excel.
- SOLAR_FIELDS 3y ago
  I worked at a company that had this (OLAP Cube + Pivot Tables) as one of my first internships and it was mind blowing. I bring this up in terms of amazing analytics tools in the data space somewhat often. Even highly developed BI tools like metabase can't handle dimensions as well as the pivot table.
Scubabear68 3y ago
They are indeed very powerful, if unfortunately named, and the UI always takes me a while to do it properly in Excel. I like how MacOS Numbers does it, the Categories feature is much easier to use, however it has limitations that are very annoying if you reach them. For anything complex these days, it is often easiest to export the spreadsheet to CSV and run SQL queries on it with CSVQ.
sorokod 3y ago
"Here’s an example of the pivot table in action." What follows reminds me of "draw the rest of the f* *ing owl" meme.
elsadek 3y ago
I new about pivot table when I was working with Python Pandas. Pandas has this feature, and it has saved me a huge amount of time.
xyzelement 3y ago
Despite being an engineer and well versed in SQL, the ability to do things quickly in Excel, including whipping up pivot tables, has been a big boost to my career, especially as I moved more to product and business management. Pivot tables let you super quickly see how things break down across dimensions and play with that analysis in a way that makes for rapid decision making that's not matched by much else other than established tools for well-defined spaces.
- WendyTheWillow 3y ago
  I’ve never gotten into pivot tables, mostly because data has always been available in a relational db for my career. But the effort to move the data into one from a spreadsheet is way overkill, so I do think it’s not suboptimal to use them even as an engineer.
  - xyzelement 3y ago
    I can see how all pivot table capabilities can be replicated via SQL but I think some of that becomes very expensive and error prone while being simple drag-drop in Excel/Sheets. The ease of doing it is a key feature. if I have to build a certain report because it's my job, I will do it whatever it takes. If I am just doing extra due diligence for myself, I may not do it if it takes hours of SQL crafting.
- chinchilla2020 3y ago
  I feel the opposite. I rapidly upload my data to bigquery and start slicing. I can fire out SQL queries way faster than I can click around in excel... and it is reproducible since it's easier to copy-paste a query from history than redo all formatting in an excel sheet later on.
  - theshrike79 3y ago
    The issue here is "click around" :) The true pros use Excel without a mouse at all.
hankyone 3y ago
Everytime I have tried to use pivot tables I end up just firing PowerBI instead, it's a much better experience.
- jimnotgym 3y ago
  It depends. It is common to use the results in further Excel calculations.
- bafe 3y ago
  I usually end up using panda or R
bloopernova 3y ago
I don't know if anyone will find this useful, but here's something I learned literally this weekend. Sort rows/cells into groups based on the value of a cell. =FILTER(Stories!B2:D13,Stories!F2:F13=A2) First parameter, "Stories!B2:D13" is a group of cells showing some stories. Second parameter, "Stories!F2:F13=A2" is the column where each cell is compared to the value of A2. Rows that match are then copied into wherever the =FILTER formula is placed. I use it to take a list of Stories and sorts them into Sprints automatically. That's useful for program increment planning, etc. The other useful Excel thing I learned recently is: =IF(NOT(ISBLANK(A2)),HYPERLINK("https://jira-instance.atlassian.net/browse/PROJECT-"&A2,"PROJECT-"&A2),"") That says: If the cell A2 is not blank, append its value onto the url given, and show that as a link with the text PROJECT- with the value of A2 appended. I know I should have probably done something cooler with Emacs and org-mode, but I have to share it with a lot of business folks. If by some chance either of those are useful to you, I hope they work OK for you :)
- layman51 3y ago
  Thanks, I really find these examples useful. For me, the QUERY function in Google Sheets has been really useful too. Same thing with IMPORTRANGE. It really helps with documenting what you did to get a count of rows.
  - bloopernova 3y ago
    I'm glad you found them useful, thank you for letting me know!
- fsckboy 3y ago
  what is a "story" in a spreadsheet?
  - codetrotter 3y ago
    > take a list of Stories and sorts them into Sprints automatically From this part it sounds to me that they are talking about a spreadsheet they have relating to Agile development
__mharrison__ 3y ago
I use this in Pandas all the time. (Just got done teaching a class about it.) Very useful once you grok the syntax. Smart folks like duckdb realize the utility (and the pain of doing this in normal SQL) and have added PIVOT to their implementation. Super useful.
- ideamotor 3y ago
  I like R’s implementation in dplyr, which is pivot_longer and pivot_wider. I’d really like to see those in Postgres.
  - bafe 3y ago
    Weren't those functions moved to tidyr? Anyway they should be taken as a model for how to implement a sane interface for data reshaping in other languages. You can say what you want about R/Wickham, but he's not afraid to thinker with his modules until they find the perfect user experience
    - ideamotor 3y ago
      true. and it used to be spread() and gather() and those were bad
      - bafe 3y ago
        Yes, the newer "pivot_wider" and "pivot_longer" are just much cleaner and nicer than the 25 different half-broken way you can use in pandas. Luckily I don't have to do a lot of data engineering anymore, because R is just not fun for large projects while pandas is very unergonomic
        ideamotor 3y ago
        It sounds like in the past you’ve used pandas/python for this. Any opinions on the best option for pivoting with those tools?
tacon 3y ago
I once read about several companies whose entire product was an Excel addin that did ... pivot tables. The average businessperson had never heard of the pivot table functionality built into Excel, and they were an easy sale for the magical demo of a pivot table on their own data.
kagakuninja 3y ago
Microsoft directly stole their pivot table from a former employer of mine, Brio Technology, and their product DataPivot. https://en.wikipedia.org/wiki/Brio_Technology https://en.wikipedia.org/wiki/Brio_Technology According to the CEO, Microsoft summoned him to Redmond and made an insultingly low offer to buy the company. They threatened to build their own product and put Brio out of business if he refused. He refused and MS later added pivot tables to Excel. The article mentions Lotus, who apparently built a similar product around the same time. The Brio founders were involved with a company called Metaphor, and maybe some of the ideas were developed there... Brio products shipped with a sample database, the contents of the CEO's wine cellar. That exact data could be seen on early Microsoft office boxes...
- riemannzeta 3y ago
  Something similar happened to Stac Electronics. https://en.wikipedia.org/wiki/Stac_Electronics https://en.wikipedia.org/wiki/Stac_Electronics But Stac had patents, lawyered up, and sued Microsoft for patent infringement. Microsoft didn't lie down, but after a court ruled that both of them needed a license to ship products, Stac went to the largest OEM customers and offered a license. It was either take the patent license or not ship, and the pressure from the OEMs on Microsoft resulted in a settlement that was not terrible for Stac. This is one of the best examples I know of how software patents can be good for competition.
  - kagakuninja 3y ago
    Brio had a patent on their pivot table aggregation algorithm, perhaps Microsoft did something differently in their implementation. I've never used an Excel pivot table.
    - canadianfella 3y ago
      [dead]
    - eps 3y ago
      According to Wikipedia Brio got their patent in 1999, i.e. well after Excel got pivot tables.
trelane 3y ago
In case folks don't yet know how to use pivot tables in LibreOffice: https://books.libreoffice.org/en/CG71/CG7108-PivotTables.html https://books.libreoffice.org/en/CG71/CG7108-PivotTables.htm...
mcsoft 3y ago
I use pivot tables all the time. The concept is brilliant, but the Excel UI leaves a lot to be desired. At first, you're amazed at the flexibility, but once you become comfortable, you suddenly hit the limitations. Can't sort by a calculated column, can't categorize without adding columns in the data, etc. I looked at Quantrix for a while, and it was a bit too complex for practical purposes. I wonder if there are any decent PivotTable tools out there?
digging 3y ago
Okay... so what is a pivot table? I've never used one or seen one used, and my browser didn't load the embedded video. What I learned about pivot tables from this article: - they are an easy way to show data that's in a spreadsheet - they were invented at Lotus (maybe) I don't even begin to know what they actually do or how someone would use one.
- airstrike 3y ago
  It's a drag-and-drop UI for grouping items in a table by category. Plenty of quick videos on YT since business users generally struggle to grasp it. Here's one example: https://www.youtube.com/watch?v=qu-AK0Hv0b4 https://www.youtube.com/watch?v=qu-AK0Hv0b4 It's paradoxically very useful and complete garbage at the same time, IMHO
  - 121789 3y ago
    Why garbage? It has limitations but is incredibly useful for what it is
    - airstrike 3y ago
      It's just that the limitations feel like whoever was implementing it walked away from it before they were finished to work on a different project. It could be much better than it is. It's way too brittle and the UX for more advanced aggregations is downright painful.
      - cdaringe 3y ago
        Ya… I fee that. The only high quality ones on the web too are proprietary. Im using the plotly react pivot that gets the job done, but doesnt even compete with excel… 2010 edition . Would love a killer end all pivot integration. I have no idea what the ideal UX is, i just know that whatever I got aint it
jimnotgym 3y ago
I am regarded as a decent Excel user, and in my field, Finance, that is saying at least something. However, I had never tried power pivot until today. I was a bit annoyed that it can't handle many to many relationships. Time to dust off my SQL skills and do it the old fashioned way... I had a table of sales transactions, and a table of stock balance. I wanted to join them on the item sold so per item I had stock balance and a sales value per sku. I was suprised it wouldn't do it. It returned in less than a second as a sell query.
- proamdev123 3y ago
  The workaround for many to many relationships isn’t too hard. What you have to do is create another table containing unique values of items sold, and then make 1:many relationships from that table to the other two. You can easily make the unique value table by copying and pasting all of the items sold into a single column on a new sheet, highlight them all, and then Data -> Drop Duplicates. It’s a little annoying, but not hard.
  - jimnotgym 3y ago
    But I have line level data that needs summing in the sales table. The workaround is one table of stock, one of sales and a sumif on the sales to get the stock. But now I don't have a pivot table
    - proamdev123 3y ago
      That’s what Power Pivot is for. Once you create the additional table and relationships as I described, you should be able to create a pivot table using data from multiple tables. No SUMIFs required. Unless I’m misunderstanding something.
bambax 3y ago
> Rather than enter formulas, users would be able to point and click to get those summary statistics. The Lotus team called this tool “flexible views,” but today similar tools are called “pivot tables” in both Microsoft Excel and Google Sheets. > The Lotus team showed Jobs an early prototype. “Steve Jobs thought it was the coolest thing ever,” Salas, now a professor at Brandeis University, tells Quartz. Jobs then convinced Lotus to develop the pivot table software exclusively for the NeXT computer. The software came out as Lotus Improv, and though the NeXT computer was a commercial failure, Lotus Improv would be hugely influential. Joel Spolsky had this to say about Improv though [0]: > When we were designing Excel 5.0, the first major release to use serious activity-based planning, we only had to watch about five customers using the product before we realized that an enormous number of people just use Excel to keep lists. They are not entering any formulas or doing any calculation at all! We hadn’t even considered this before. Keeping lists turned out to be far more popular than any other activity with Excel. And this led us to invent a whole slew of features that make it easier to keep lists: easier sorting, automatic data entry, the AutoFilter feature which helps you see a slice of your list, and multi-user features which let several people work on the same list at the same time while Excel automatically reconciles everything. > While Excel 5 was being designed, Lotus had shipped a “new paradigm” spreadsheet called Improv. According to the press releases, Improv was a whole new generation of spreadsheet, which was going to blow away everything that existed before it. For various strange reasons, Improv was first available on the NeXT, which certainly didn’t help its sales, but a lot of smart people believed that Improv would be to NeXT as VisiCalc was to the Apple II: it would be the killer app that made people go out and buy all new hardware just to run one program. > Of course, Improv is now a footnote in history. Search for it on the web, and the only links you’ll find are from very over-organized storeroom managers who have, for some reason, made a web site with an inventory of all the stuff they have collecting dust. > Why? Because in Improv, it was almost impossible to just make lists. The Improv designers thought that people were using spreadsheets to create complicated multi-dimensional financial models. Turns out, if they asked people, they would discover that making lists was so much more common than multi-dimensional financial models, and in Improv, making lists was a downright chore, if not impossible. [0] https://www.joelonsoftware.com/2000/05/09/the-process-of-designing-a-product/ https://www.joelonsoftware.com/2000/05/09/the-process-of-des... - - - I don't know if pivot tables are cool; one big problem that people often overlook is that they have to be recalculated ("refreshed") manually; this can lead to significant errors. Conditional sums are not "complex formulas", they are often easier to understand and debug than pivot tables -- and they are recomputed with each change, which will eventually save your ass.
caycep 3y ago
It is basically Excel's way of bringing matrix math to the masses
mritchie712 3y ago
For more power: QUERY() https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/ https://www.benlcollins.com/spreadsheets/google-sheets-query...
cgio 3y ago
Great feature but also a very dangerous one. Few cases of people sharing a spreadsheet with an innocuous summary pivot table, not realising they also, in the process, share the full dataset that supports the pivot with sensitive data.
- aendruk 3y ago
  I once had an instructor do this with a histogram of grades in slides shared with the class.
hbarka 3y ago
Pivot tables are powerful because they operate on the principle of multidimensional analysis, an intuitive framework for measuring (facts) by categories (dimensions). Facts and dimensions are abstracted into an organized calculation model also known as cubes or OLAP or star schema.
pama 3y ago
One problem with pivot and its reverse operation is the poor terminology surrounding them. In addition to pivot itself, people used unstack, stack, gather, melt, reshape, transpose, … A single reversible concept like pivot_wider and pivot_longer from tidyverse, really helps clarity and I wish other libraries also start using this better terminology.
WillAdams 3y ago
Or, you could use pyspread and list comprehensions for a similar effect: https://pyspread.gitlab.io/ https://pyspread.gitlab.io/ https://pyspread.gitlab.io/manual/advanced_topics.html https://pyspread.gitlab.io/manual/advanced_topics.html
narush 3y ago
I'm building an open-core spreadsheet currently, and pivot tables are by far our number-one data transformation feature. By like 3x-1x. This is compared to even formulas (shockingly! but this is also a function of our audience). In the hundreds of data-science-in-Excel files I've seen, I can't think of a single one that doesn't make use of a pivot table in some way. (https://trymito.io https://trymito.io, if you're interested)
jerry1979 3y ago
Pivot tables are amazing to hand off to competent users to let them poke around at their data, but sometimes I would not want them to swap rows and columns because it would wreck some calculation I was doing. So, I used some vba to lock down the certain functionality. I think this page has some of the techniques I used: https://spreadsheetplanet.com/lock-pivot-table-excel/ https://spreadsheetplanet.com/lock-pivot-table-excel/
didgetmaster 3y ago
I like pivot tables. When I was developing my own database technology, I found that it could create pivot tables very efficiently. I put a lot of work into refining that feature; but almost everyone I show the demo to does not seem to see much value in it. What am I missing? Here is a short video where I use it to analyze data within an 8 million row table: https://www.youtube.com/watch?v=2ScBd-71OLQ https://www.youtube.com/watch?v=2ScBd-71OLQ
ggm 3y ago
It is probably just a side effect of how I read the docs, but every time I need one of these I have to re-learn how, and for some reason the tooling in Excel always wants to do some 'other' orientation of my data. If I consider my data as a set of rows (instances) against columns (values) it somehow winds up mis-interpreting the values as the instances. Mind you, this also happens when I select regions of cells for charting. Maybe order of selection matters.
WiSaGaN 3y ago
It seems no one mentioned this. Joel Spolsky had this famous "lecture": You Suck at Excel with Joel Spolsky [1], where the talk about pivot table starts at 50:41 [2]. [1] https://youtu.be/0nbkaYsR94c https://youtu.be/0nbkaYsR94c [2] https://youtu.be/0nbkaYsR94c?t=3041 https://youtu.be/0nbkaYsR94c?t=3041
- chrisfinazzo 3y ago
  If you've never really grokked spreadsheets like Excel, this is a great place to start. Although I don't have Office installed, most of what Joel demos can be done in Numbers if you are so inclined. It's not nearly as powerful as Excel, but for my needs it's capable enough. As regards Pivot Tables, I think Numbers had them at some point early in its history, but it wasn't 1.0 - Notably, Apple's implementation was significantly different than Excel and people complained, loudly enough that it was rewritten to be more compatible with MS in a later version. The elephant in the room is (unsurprisingly) SPSS, which I had the unfortunate luck to encounter in my Research Methods class during grad school. Since that time (2010-2012), many of the other frameworks and tools have emerged to either fill in gaps or do the kinds of analysis that even Excel isn't well suited for.
paulbares 3y ago
I found it too cumbersome, limited and not very intuitive to do it in SQL with the PIVOT operator so that I created my own API to be able to build a pivot table from a simple SQL statement https://github.com/squashql/squashql-showcase/blob/main/TUTORIAL.md#pivot-table https://github.com/squashql/squashql-showcase/blob/main/TUTO.... Roughly speaking, it consists in a single SQL statement and a list of headers for the rows and columns.
TheRealDunkirk 3y ago
I once wrote a time-and-attendance app for a small company I worked for (using Rails 2.x). I put an export function in it, so that the office manager could dump the week's hours, pull it into Excel, and run pivots on person-time-per-project (to pay employees) and project-time-per-customer (to bill customers). After a few weeks, it was clear that the office manager wasn't "getting" how to do this, despite a couple of explanations, so I removed the export, and simply generated both pivot tables in a page. This solved the payment/billing problem so well, that, last I heard, they were still using this application 8 years after I left.
zubairq 3y ago
I feel really stupid as I've been a programmer for 40 years and never used a pivot table. Am I the only one?

342 points by airstrike 3y ago

Bzhf

It's too bad that the pivot table is a poor approximation of a true multidimensional spreadsheet, for example Lotus Improv: https://instadeq.com/blog/posts/no-code-history-lotus-improv-spreadsheets-done-right-1991/ https://instadeq.com/blog/posts/no-code-history-lotus-improv...

qsort 3y ago
Which is itself the poor man's groupby. At some point you've just got to admit you have the wrong abstraction. Strong Zalgo vibes.
- airstrike 3y ago
  Or at some point you realize we need some new abstraction ;-)
- jasode 3y ago
  >Which is itself the poor man's groupby. SQL GROUP BY creates summarized horizontal rows. Instead, pivot tables are more analogous to crosstab queries which creates summarized vertical columns. It "pivots" data groupings by rotating from horizontal to vertical. The older versions of SQL dialects that didn't have the newer cross tab syntax required convoluted CASE syntax to "simulate" pivot tables which didn't really work that well since one had to know ahead of time -- all the unique values -- to put in each CASE condition branch.
  - qsort 3y ago
    If you are grouping over them, they shouldn't be columns in the first place, the original SQL is "right". Rows and columns aren't symmetric! Reshaping data should be a presentation-time decision, not a query-time decision. You have a dataset, a relation in the algebraic sense, and you are choosing to display it in some way: as a table, as a pivoted table, as a pie-chart... Conflating the two is a consequence of spreadsheets having an unbeatable UX but a terrible data model that lets you treat rows as columns and vice-versa.
    - fifilura 3y ago
      There are real use cases for pivoting data in SQL not only for presentation. Tables can be too "long". You need data points to be on the same row if you want to do arithmetic operations on on different types of values. For example k + 3.14* number_of_chimneys - 0.13 * age - 1.34*neigbourhood_criminality as house_price
    - toyg 3y ago
      > Reshaping data should be a presentation-time decision When you potentially billions or trillions of data points, it isn't. Rows and columns have limits. You need some hard logic for true multidimensional data at scale.
      - quantified 3y ago
        For business analyst purposes, the reshape should be a display-time activity. Finance models may start with billions but tend to present a manageable quantity to the user. The chain of calculations back to any stored data is the real key, along with what your interactivity needs for multi-user update are.
    - tomnipotent 3y ago
      > should be a presentation-time decision, not a query-time decision Agree, but sometimes you just need to shove the results of a SQL query into an Excel file and you don't want to get fancy. You're either 1) overwriting Sheet B and then using a pivot table in Sheet A to get the final presentation, 2) pivoting in the code/program executing the query before writing to Excel or 3) pivoting in SQL and skipping the code and Excel pivot table altogether. I run into this a lot with data used for financial modeling, or financial reporting that heavily relies on using dates/categories as column/row headers.
- contravariant 3y ago
  Goupby may be the higher abstraction, but it's not necessarily better. Dimensional models are basically modules (generalised linear spaces), a groupby can do the same things but doesn't really give much useful structure to work with (at best the result is ordet independent, most of the time). This is also why sums and counts tend to be more useful than averages.
- quantified 3y ago
  They're different. Group by only gets you so far. Actual modeling of business scenarios deals with a lot of irregularity and the specialized spreadsheet-like tools were mostly much much better at expressing this.
vondur 3y ago
The linked article specifically mentions Lotus Improv as the app that had this functionality in it. Interesting how Steve Jobs was able to get Lotus to make it a NeXT exclusive app initially.
RGamma 3y ago
Sounds a lot like what you can do with tabular model (PowerPivot) and DAX (measures) in Excel now.
Brian_K_White 3y ago
The article says Improv is where pivot tables started.
dannyobrien 3y ago
I remember going to the UK launch of Lotus Improv as a newbie journalist. It was really notable how both how flashy and professional it was compared to other products (in retrospect, I'm presuming that was Jobs' influence). Nonetheless, I think they really struggled to explain pivot tables, and why, in itself, that feature was sufficient to move to a new application and a new hardware platform.
- fractallyte 3y ago
  Improv included an excellent animated presentation which explained its features perfectly. The problem was to get new users to sit their asses down and actually watch it. In hindsight, this was obviously a UX failure - but I equally blame users who lacked any attention span.
fiddlerwoaroof 3y ago
I believe this is a sort of descendant of Improv: https://quantrix.com/products/quantrix-modeler/ https://quantrix.com/products/quantrix-modeler/
- steve1977 3y ago
  It is, and like Improv, has its roots in NeXSTSTEP. http://www.kevra.org/TheBestOfNext/ThirdPartyProducts/ThirdPartySoftware/PersonalProductivity/SpreadSheet-Database/Quantrix/Quantrix.html http://www.kevra.org/TheBestOfNext/ThirdPartyProducts/ThirdP... There used to be a version of Quantrix Modeler that was affordable for „home users“, it’s been quite a while though.
ghaff 3y ago
One of the "crimes" (he types hyperbolically) of Microsoft Office is that is basically sucked all the air out of the room for anything else in the non-graphical artist office productivity area that wasn't Office or a pretty direct knock-off. The spreadsheet model is a good example (even if Excel is probably the best thing in Microsoft Office. But it also means that if you can't make Word do a good enough job for desktop publishing you generally have to go to InDesign which is probably way overkill if yoiu're not a publishing professional.
- znpy 3y ago
  To be honest i saw a teacher in high school working on his own textbook)the second revision of an already published textbook) in word and while it had the classic wysiwyg experience, it was typographically okay, almost ready to be printed.
  - ghaff 3y ago
    Word, or even something like Google Docs which lacks some features in areas like Section numbering, isn't terrible. I published a book using Google Docs and basically decided anything it couldn't do I didn't need or could handle manually. But, if I were actually come up with a wish list for a low-end publishing platform it would probably look a bit different than Word.
- firecall 3y ago
  A widely shared article from a few years back claimed that Excel was the world's most popular design tool. I googled - I couldnt find the article :-) I think it was in the Guardian or some such.
  - tnecniv 3y ago
    I’d believe it if you count every random plot people make as “design.” It blows my mind how much people do in excel. On one hand it’s pretty cool how much it can do but you end up with these monster spreadsheets that should really be their own program of some sorts. One example I think about a lot (because I use the end product a lot) is Fangraphs’ ZiPs model that predicts baseball stats for upcoming seasons. It’s 20 years old and, from what I’ve been told, a massive Excel book with some VB. The creator was a stats major but they didn’t do much programming in his curriculum so Excel was the option he was most comfortable / productive with. Yet, he’s doing a massive analysis over every player in the league using decades of historical data. The thought of doing that in Excel makes my stomach churn but if it works for him then I guess it’s good enough!
II2II 3y ago
I have not used spreadsheets very often since the mid-1990's. One of the reasons: I was excited by the potential of Improv, but disappointed when I realized that it had no future. Little did I realize that pivot tables were a different take on the concept! (The other reason for abandoning spreadsheets was performance. I forget how good/bad Improv was in this respect, but I doubt that I would have stuck with spreadsheets since the data sets I was dealing with weren't really appropriate for them.)
huhtenberg 3y ago
Half of the QZ article is literally about Salas and Improv.
re5i5tor 3y ago
On NeXT 2 years before Windows
getravi 3y ago
Never knew Improv existed. There are tools that are similar to the vision of Improv. I use Anaplan at work everyday and it is exactly what a modern cloud based SaaS version of Improv would feel like. It is a multi billion dollar company and it worked because it did not go after the spreadsheet space but played along nicely with it. The Improv article concludes "the key strategy mistake was to try to market Improv to the existing spreadsheet market. Instead, if the product were marketed to a segment where the more structured model was a ‘feature’ not a ‘bug’ would have given Lotus the time to learn and improve and refine the model to a point where it would have satisfied the larger market as well." and Anaplan seems to not have made this mistake. They have carved out a niche in the EPM (Enterprise Performance Management) market.
- quantified 3y ago
  Improv's model was poor though, it was based on cell positions and it was easy to double-count. Basically was bad at various semantic aspects. I worked on a small conpetitor back in the day and we competed on modelling, there were a number of others too. I've also worked on Anaplan and and its modeling is also much better, so please don't lower it to any version of Improv! It isn't, or at least wasn't a little while back, good at cross-metric ("line item" to them") calculations and presentations, but still easier to get something complex correct. The real mistake of Improv was that it wasn't 1-2-3 so Lotus didn't know how to narket it or sell it.
kagakuninja 3y ago
You may want to read about DataPivot, developed by Brio Technology during the same period as Lotus. Brio had a patent on the pivot data aggregation algorithm, I'm not sure how Lotus's pivot table worked... https://en.wikipedia.org/wiki/Brio_Technology https://en.wikipedia.org/wiki/Brio_Technology
somat 3y ago
I use postgres in the role of a better* spreadsheet. Huge asterisk: Better is a very subjective, Ad-hoc data entry is terrible by comparison, but bulk operations are much nicer. The real reason for the change was that I was starting to loathe the spreadsheet data model. I am not fond of how easy it is screw up data in the big bag of cells data model that spreadsheets offer. The core feature I really wanted is row level security, for rows to to stick together.
- ElectricalUnion 3y ago
  The missing part "better spreadsheet" for me is some standard, simplified reactive evaluation declaration syntax. I'm probably wrong, of course. Oracle has a pretty crazy feature where it handles a query like a temporary spreadsheet where you can enact reactive queries upon [1], that I haven't found in other SQL DBs. I know you can do that using window functions + recursive CTEs but under this "reactive evaluation of a sub-query" use case they tend to get ugly and incomprehensible real fast. [1] https://www.oracle.com/webfolder/technetwork/tutorials/obe/db/10g/r2/prod/bidw/sqlmodel/sqlmodel_otn.htm https://www.oracle.com/webfolder/technetwork/tutorials/obe/d...

klysm 3y ago

Just let me write SQL please

DanAtC 3y ago
Dynamically pivoting SQL is a pain in most dialects
- breadwinner 3y ago
  You can write a normal SELECT * FROM FOO query and use a tool like Pebble Reports to do the pivoting: http://www.pebblereports.com/videos/crosstab/ http://www.pebblereports.com/videos/crosstab/
ChrisClark 3y ago
You can in Google Sheets. Well, close enough at least. Use the QUERY function.
whalesalad 3y ago
duckdb will let you do both! https://duckdb.org/docs/sql/statements/pivot.html https://duckdb.org/docs/sql/statements/pivot.html
yunohn 3y ago
IME pivoting on SQL results is a very common usecase too.
chasd00 3y ago
ignorance is bliss but you should learn to work all your tools well not just rely on one.
baq 3y ago
common use case: get a metric truckload of data from a column store binned per minute (or 5), give the giant sheet to users, users use pivot tables to create hourly/daily/weekly reports as they see fit. (not all users are dumb it turns out)
jabroni_salad 3y ago

strongpigeon 3y ago

I remember in Google Ads, we’d build all these cool looking dashboards but whenever we did user research it was pretty apparent that all they wanted was their data in a pivot table…

c4mpute 3y ago
All these cool-looking dashboards are just too inflexible. You cannot add your own aggragates beyond trivialities. You cannot just "color that one value that bugs you". You cannot just generate a readable report plus some explanatory text. Spreadsheet export + pivot table gives you all that. Doable for any moderately competent office drone without a round-trip through some endless backlog-spec-sprint-program-test-respec-sprint-... loop
- c4mpute 3y ago
  To be somewhat constructive: What you rather should have done is not create more elaborate dashboards. What imho the world needs is an easy way to use a spreadsheet tool to generate and publish a dashboard. A "make web dashboard" button right next to the print button. With auto-updates when input data changes of course.
  - airstrike 3y ago
    Yup. That's what I want to build. Thank you for saying that -- I feel like it really validates my feelings hah
    - fiddlerwoaroof 3y ago
      The problem is always this project turns into “let’s build excel or tableau” and the customers that care usually already use one or the other.
      - airstrike 3y ago
        That's fair, but fortunately I'm not planning on doing either. (Well, I am still implementing ~all of Excel's formulas for compatibility, but not the the UI/UX...) People don't really consume data, they read documents. I think that's (part of) the vision these projects lack.

gadders 3y ago

I was working at Lotus when Improv came out. From what I remember, it was popular but as you started adding dimensions memory use blew up. This was in the early days of Windows and 4mb of memory.

kagakuninja 3y ago
And I was working at Brio Technology in the late 90s, they had a similar product called DataPivot, first sold in 1991. https://en.wikipedia.org/wiki/Brio_Technology https://en.wikipedia.org/wiki/Brio_Technology I don't remember Brio's pivot tables "blowing up" per-se, but I suppose computers had a lot more memory by the time I joined. They used a patented algorithm to create the pivot structure by aggregating a result-set from an SQL query.

asah 3y ago

nice! AI is about to end this - shortly, we'll be able to ask for the answer directly, in zero clicks.

Brian_K_White 3y ago
and have no confidence in it
rchaud 3y ago
Perhaps we could create a set of logical prompts to get there even faster. We could call it a structured query language!
- mxuribe 3y ago
  Or better yet: conversation query language (CQL)! ...Though the risk exists for verbal name collision. ;-)
Bishonen88 3y ago
Dunno, seems to me that asking (writing in this case) is not final solution. In fact, in most cases, typing down a question will take longer than looking in a well created table. This chat-like back-and-forth, will take forever to understand some dataset properly. One question answered will give birth to 5 additional questions and so on.
airstrike 3y ago
this is unfairly downvoted, IMHO -- i doubt it will take "zero clicks" -- we're more likely replacing clicks with prompts -- but LLMs can definitely help bridge the gap between data wrangling and presentation. replies to this comment seem to overvalue the challenges without acknowledging that those may eventually be overcome.
- seektable 3y ago
  search-driven analytics is not really a new thing and products in this space were before LLM-era. This kind of interface can be useful for some categories of users but it is not a game-changer - prompts cannot replace Excel and its pivot tables, and in fact typing prompts may be even more complicated for users than good old 'clicks'.

thrdbndndn 3y ago

this probably is more about specific implementations than pivot table in general, but I often find it's "lacking" when wanting to do some more advanced things with it. To name a few: The way how the "values" are generated is very limited in both Excel and Google Sheets (in different ways). The way how filter/sorting works with pivot table isn't the most straightforward or flexible. the "UI" elements (headers, styles, etc.) is very hard to control. I often find myself creating a pivot table, and copy it somewhere else, and manually fix bunch of stuff -- which kinda defeats the point of it (since I can no longer dynamically update it). Disclaimer: I'm by no means a spreadsheet expert so I may just miss something.

airstrike 3y ago
Agreed, current implementations are totally brittle and far from "feature complete" relative to what an experienced / power user would expect coming in It actually bothers me so much I've decided to write my own spreadsheet engine. This is one of the pain points I want to fix
seektable 3y ago
You may try these online pivot tables https://www.seektable.com https://www.seektable.com where you can re-order rows/columns simply with a click on the header, apply filters via simple input where you can specify which items to keep or exclude.
conductr 3y ago
I’m pretty close to a spreadsheet expert, I categorize pivot tables as just quick and dirty adhoc/disposable data visualization and never would use it as something for presentation or where styling mattered. I usually use it to quickly find unexpected values in the underlying data columns. Although with spill formulas like =unique() I’m using it less and less for this. I find often people wanting more out of it, are really asking for Power Query. Which is there, just a lot of people are intimidated by. (Maybe not HN, but general population)

Qem 3y ago

The second most powerful is probably the Solver, that few programming languages (like prolog) include equivalent functionality out of the box. That's one thing that consistently gets me crawling back from Python/Pharo to Calc.

tqi 3y ago

Pivot tables powered by SSAS cubes may be the best self serve analytics tool I've ever seen (where "best" is measured by how much users actually use it). The ability to meet business users where they are is huge for actual adoption, especially compared to something like Looker (which is hot garbage for other reasons as well). Plus, 9 times out of 10 people want to apply additional lightweight transformations/calculations to the data, so nothing beats being in Excel already. Too bad OSX support is non existent and writing MDX is a pain in the fucking ass.

doppenhe 3y ago
this makes me happy , I was the PM for that feature :)
davio 3y ago
Back in the mid 2000s, our killer app was an example app from MSDN magazine. It basically embedded Excel in a simple web page and we could use the pivot tables against SSAS cubes. We did a little web work for permissions and to save views, but probably had less than a total week of dev work.
hermitcrab 3y ago
Have you tried Easy Data Transform? It is a lightweight ETL tool for doing data transformations (such as pivot) on Excel, CSV and various other file formats. Runs natively on Windows and Mac.
- tqi 3y ago
  The ETL part is not really the problem business users seem to have, but rather needing the ability to use pre-built measures / dimensions natively in Excel.
SOLAR_FIELDS 3y ago
I worked at a company that had this (OLAP Cube + Pivot Tables) as one of my first internships and it was mind blowing. I bring this up in terms of amazing analytics tools in the data space somewhat often. Even highly developed BI tools like metabase can't handle dimensions as well as the pivot table.

Scubabear68 3y ago

They are indeed very powerful, if unfortunately named, and the UI always takes me a while to do it properly in Excel. I like how MacOS Numbers does it, the Categories feature is much easier to use, however it has limitations that are very annoying if you reach them. For anything complex these days, it is often easiest to export the spreadsheet to CSV and run SQL queries on it with CSVQ.

sorokod 3y ago

"Here’s an example of the pivot table in action." What follows reminds me of "draw the rest of the f* *ing owl" meme.

elsadek 3y ago

I new about pivot table when I was working with Python Pandas. Pandas has this feature, and it has saved me a huge amount of time.

xyzelement 3y ago

Despite being an engineer and well versed in SQL, the ability to do things quickly in Excel, including whipping up pivot tables, has been a big boost to my career, especially as I moved more to product and business management. Pivot tables let you super quickly see how things break down across dimensions and play with that analysis in a way that makes for rapid decision making that's not matched by much else other than established tools for well-defined spaces.

WendyTheWillow 3y ago
I’ve never gotten into pivot tables, mostly because data has always been available in a relational db for my career. But the effort to move the data into one from a spreadsheet is way overkill, so I do think it’s not suboptimal to use them even as an engineer.
- xyzelement 3y ago
  I can see how all pivot table capabilities can be replicated via SQL but I think some of that becomes very expensive and error prone while being simple drag-drop in Excel/Sheets. The ease of doing it is a key feature. if I have to build a certain report because it's my job, I will do it whatever it takes. If I am just doing extra due diligence for myself, I may not do it if it takes hours of SQL crafting.
chinchilla2020 3y ago
I feel the opposite. I rapidly upload my data to bigquery and start slicing. I can fire out SQL queries way faster than I can click around in excel... and it is reproducible since it's easier to copy-paste a query from history than redo all formatting in an excel sheet later on.
- theshrike79 3y ago
  The issue here is "click around" :) The true pros use Excel without a mouse at all.

hankyone 3y ago

Everytime I have tried to use pivot tables I end up just firing PowerBI instead, it's a much better experience.

jimnotgym 3y ago
It depends. It is common to use the results in further Excel calculations.
bafe 3y ago
I usually end up using panda or R

bloopernova 3y ago

I don't know if anyone will find this useful, but here's something I learned literally this weekend. Sort rows/cells into groups based on the value of a cell. =FILTER(Stories!B2:D13,Stories!F2:F13=A2) First parameter, "Stories!B2:D13" is a group of cells showing some stories. Second parameter, "Stories!F2:F13=A2" is the column where each cell is compared to the value of A2. Rows that match are then copied into wherever the =FILTER formula is placed. I use it to take a list of Stories and sorts them into Sprints automatically. That's useful for program increment planning, etc. The other useful Excel thing I learned recently is: =IF(NOT(ISBLANK(A2)),HYPERLINK("https://jira-instance.atlassian.net/browse/PROJECT-"&A2,"PROJECT-"&A2),"") That says: If the cell A2 is not blank, append its value onto the url given, and show that as a link with the text PROJECT- with the value of A2 appended. I know I should have probably done something cooler with Emacs and org-mode, but I have to share it with a lot of business folks. If by some chance either of those are useful to you, I hope they work OK for you :)

layman51 3y ago
Thanks, I really find these examples useful. For me, the QUERY function in Google Sheets has been really useful too. Same thing with IMPORTRANGE. It really helps with documenting what you did to get a count of rows.
- bloopernova 3y ago
  I'm glad you found them useful, thank you for letting me know!
fsckboy 3y ago
what is a "story" in a spreadsheet?
- codetrotter 3y ago
  > take a list of Stories and sorts them into Sprints automatically From this part it sounds to me that they are talking about a spreadsheet they have relating to Agile development

__mharrison__ 3y ago

I use this in Pandas all the time. (Just got done teaching a class about it.) Very useful once you grok the syntax. Smart folks like duckdb realize the utility (and the pain of doing this in normal SQL) and have added PIVOT to their implementation. Super useful.

ideamotor 3y ago
I like R’s implementation in dplyr, which is pivot_longer and pivot_wider. I’d really like to see those in Postgres.
- bafe 3y ago
  Weren't those functions moved to tidyr? Anyway they should be taken as a model for how to implement a sane interface for data reshaping in other languages. You can say what you want about R/Wickham, but he's not afraid to thinker with his modules until they find the perfect user experience
  - ideamotor 3y ago
    true. and it used to be spread() and gather() and those were bad
    - bafe 3y ago
      Yes, the newer "pivot_wider" and "pivot_longer" are just much cleaner and nicer than the 25 different half-broken way you can use in pandas. Luckily I don't have to do a lot of data engineering anymore, because R is just not fun for large projects while pandas is very unergonomic
      - ideamotor 3y ago
        It sounds like in the past you’ve used pandas/python for this. Any opinions on the best option for pivoting with those tools?

tacon 3y ago

I once read about several companies whose entire product was an Excel addin that did ... pivot tables. The average businessperson had never heard of the pivot table functionality built into Excel, and they were an easy sale for the magical demo of a pivot table on their own data.

kagakuninja 3y ago

Microsoft directly stole their pivot table from a former employer of mine, Brio Technology, and their product DataPivot. https://en.wikipedia.org/wiki/Brio_Technology https://en.wikipedia.org/wiki/Brio_Technology According to the CEO, Microsoft summoned him to Redmond and made an insultingly low offer to buy the company. They threatened to build their own product and put Brio out of business if he refused. He refused and MS later added pivot tables to Excel. The article mentions Lotus, who apparently built a similar product around the same time. The Brio founders were involved with a company called Metaphor, and maybe some of the ideas were developed there... Brio products shipped with a sample database, the contents of the CEO's wine cellar. That exact data could be seen on early Microsoft office boxes...

riemannzeta 3y ago
Something similar happened to Stac Electronics. https://en.wikipedia.org/wiki/Stac_Electronics https://en.wikipedia.org/wiki/Stac_Electronics But Stac had patents, lawyered up, and sued Microsoft for patent infringement. Microsoft didn't lie down, but after a court ruled that both of them needed a license to ship products, Stac went to the largest OEM customers and offered a license. It was either take the patent license or not ship, and the pressure from the OEMs on Microsoft resulted in a settlement that was not terrible for Stac. This is one of the best examples I know of how software patents can be good for competition.
- kagakuninja 3y ago
  Brio had a patent on their pivot table aggregation algorithm, perhaps Microsoft did something differently in their implementation. I've never used an Excel pivot table.
  - canadianfella 3y ago
    [dead]
  - eps 3y ago
    According to Wikipedia Brio got their patent in 1999, i.e. well after Excel got pivot tables.

trelane 3y ago

In case folks don't yet know how to use pivot tables in LibreOffice: https://books.libreoffice.org/en/CG71/CG7108-PivotTables.html https://books.libreoffice.org/en/CG71/CG7108-PivotTables.htm...

mcsoft 3y ago

I use pivot tables all the time. The concept is brilliant, but the Excel UI leaves a lot to be desired. At first, you're amazed at the flexibility, but once you become comfortable, you suddenly hit the limitations. Can't sort by a calculated column, can't categorize without adding columns in the data, etc. I looked at Quantrix for a while, and it was a bit too complex for practical purposes. I wonder if there are any decent PivotTable tools out there?

digging 3y ago

Okay... so what is a pivot table? I've never used one or seen one used, and my browser didn't load the embedded video. What I learned about pivot tables from this article: - they are an easy way to show data that's in a spreadsheet - they were invented at Lotus (maybe) I don't even begin to know what they actually do or how someone would use one.

airstrike 3y ago
It's a drag-and-drop UI for grouping items in a table by category. Plenty of quick videos on YT since business users generally struggle to grasp it. Here's one example: https://www.youtube.com/watch?v=qu-AK0Hv0b4 https://www.youtube.com/watch?v=qu-AK0Hv0b4 It's paradoxically very useful and complete garbage at the same time, IMHO
- 121789 3y ago
  Why garbage? It has limitations but is incredibly useful for what it is
  - airstrike 3y ago
    It's just that the limitations feel like whoever was implementing it walked away from it before they were finished to work on a different project. It could be much better than it is. It's way too brittle and the UX for more advanced aggregations is downright painful.
    - cdaringe 3y ago
      Ya… I fee that. The only high quality ones on the web too are proprietary. Im using the plotly react pivot that gets the job done, but doesnt even compete with excel… 2010 edition . Would love a killer end all pivot integration. I have no idea what the ideal UX is, i just know that whatever I got aint it

jimnotgym 3y ago

I am regarded as a decent Excel user, and in my field, Finance, that is saying at least something. However, I had never tried power pivot until today. I was a bit annoyed that it can't handle many to many relationships. Time to dust off my SQL skills and do it the old fashioned way... I had a table of sales transactions, and a table of stock balance. I wanted to join them on the item sold so per item I had stock balance and a sales value per sku. I was suprised it wouldn't do it. It returned in less than a second as a sell query.

proamdev123 3y ago
The workaround for many to many relationships isn’t too hard. What you have to do is create another table containing unique values of items sold, and then make 1:many relationships from that table to the other two. You can easily make the unique value table by copying and pasting all of the items sold into a single column on a new sheet, highlight them all, and then Data -> Drop Duplicates. It’s a little annoying, but not hard.
- jimnotgym 3y ago
  But I have line level data that needs summing in the sales table. The workaround is one table of stock, one of sales and a sumif on the sales to get the stock. But now I don't have a pivot table
  - proamdev123 3y ago
    That’s what Power Pivot is for. Once you create the additional table and relationships as I described, you should be able to create a pivot table using data from multiple tables. No SUMIFs required. Unless I’m misunderstanding something.

bambax 3y ago

> Rather than enter formulas, users would be able to point and click to get those summary statistics. The Lotus team called this tool “flexible views,” but today similar tools are called “pivot tables” in both Microsoft Excel and Google Sheets. > The Lotus team showed Jobs an early prototype. “Steve Jobs thought it was the coolest thing ever,” Salas, now a professor at Brandeis University, tells Quartz. Jobs then convinced Lotus to develop the pivot table software exclusively for the NeXT computer. The software came out as Lotus Improv, and though the NeXT computer was a commercial failure, Lotus Improv would be hugely influential. Joel Spolsky had this to say about Improv though [0]: > When we were designing Excel 5.0, the first major release to use serious activity-based planning, we only had to watch about five customers using the product before we realized that an enormous number of people just use Excel to keep lists. They are not entering any formulas or doing any calculation at all! We hadn’t even considered this before. Keeping lists turned out to be far more popular than any other activity with Excel. And this led us to invent a whole slew of features that make it easier to keep lists: easier sorting, automatic data entry, the AutoFilter feature which helps you see a slice of your list, and multi-user features which let several people work on the same list at the same time while Excel automatically reconciles everything. > While Excel 5 was being designed, Lotus had shipped a “new paradigm” spreadsheet called Improv. According to the press releases, Improv was a whole new generation of spreadsheet, which was going to blow away everything that existed before it. For various strange reasons, Improv was first available on the NeXT, which certainly didn’t help its sales, but a lot of smart people believed that Improv would be to NeXT as VisiCalc was to the Apple II: it would be the killer app that made people go out and buy all new hardware just to run one program. > Of course, Improv is now a footnote in history. Search for it on the web, and the only links you’ll find are from very over-organized storeroom managers who have, for some reason, made a web site with an inventory of all the stuff they have collecting dust. > Why? Because in Improv, it was almost impossible to just make lists. The Improv designers thought that people were using spreadsheets to create complicated multi-dimensional financial models. Turns out, if they asked people, they would discover that making lists was so much more common than multi-dimensional financial models, and in Improv, making lists was a downright chore, if not impossible. [0] https://www.joelonsoftware.com/2000/05/09/the-process-of-designing-a-product/ https://www.joelonsoftware.com/2000/05/09/the-process-of-des... - - - I don't know if pivot tables are cool; one big problem that people often overlook is that they have to be recalculated ("refreshed") manually; this can lead to significant errors. Conditional sums are not "complex formulas", they are often easier to understand and debug than pivot tables -- and they are recomputed with each change, which will eventually save your ass.

caycep 3y ago

It is basically Excel's way of bringing matrix math to the masses

mritchie712 3y ago

For more power: QUERY() https://www.benlcollins.com/spreadsheets/google-sheets-query-sql/ https://www.benlcollins.com/spreadsheets/google-sheets-query...

cgio 3y ago

Great feature but also a very dangerous one. Few cases of people sharing a spreadsheet with an innocuous summary pivot table, not realising they also, in the process, share the full dataset that supports the pivot with sensitive data.

aendruk 3y ago
I once had an instructor do this with a histogram of grades in slides shared with the class.

hbarka 3y ago

Pivot tables are powerful because they operate on the principle of multidimensional analysis, an intuitive framework for measuring (facts) by categories (dimensions). Facts and dimensions are abstracted into an organized calculation model also known as cubes or OLAP or star schema.

pama 3y ago

One problem with pivot and its reverse operation is the poor terminology surrounding them. In addition to pivot itself, people used unstack, stack, gather, melt, reshape, transpose, … A single reversible concept like pivot_wider and pivot_longer from tidyverse, really helps clarity and I wish other libraries also start using this better terminology.

WillAdams 3y ago

Or, you could use pyspread and list comprehensions for a similar effect: https://pyspread.gitlab.io/ https://pyspread.gitlab.io/ https://pyspread.gitlab.io/manual/advanced_topics.html https://pyspread.gitlab.io/manual/advanced_topics.html

narush 3y ago

I'm building an open-core spreadsheet currently, and pivot tables are by far our number-one data transformation feature. By like 3x-1x. This is compared to even formulas (shockingly! but this is also a function of our audience). In the hundreds of data-science-in-Excel files I've seen, I can't think of a single one that doesn't make use of a pivot table in some way. (https://trymito.io https://trymito.io, if you're interested)

jerry1979 3y ago

Pivot tables are amazing to hand off to competent users to let them poke around at their data, but sometimes I would not want them to swap rows and columns because it would wreck some calculation I was doing. So, I used some vba to lock down the certain functionality. I think this page has some of the techniques I used: https://spreadsheetplanet.com/lock-pivot-table-excel/ https://spreadsheetplanet.com/lock-pivot-table-excel/

didgetmaster 3y ago

I like pivot tables. When I was developing my own database technology, I found that it could create pivot tables very efficiently. I put a lot of work into refining that feature; but almost everyone I show the demo to does not seem to see much value in it. What am I missing? Here is a short video where I use it to analyze data within an 8 million row table: https://www.youtube.com/watch?v=2ScBd-71OLQ https://www.youtube.com/watch?v=2ScBd-71OLQ

ggm 3y ago

It is probably just a side effect of how I read the docs, but every time I need one of these I have to re-learn how, and for some reason the tooling in Excel always wants to do some 'other' orientation of my data. If I consider my data as a set of rows (instances) against columns (values) it somehow winds up mis-interpreting the values as the instances. Mind you, this also happens when I select regions of cells for charting. Maybe order of selection matters.

WiSaGaN 3y ago

It seems no one mentioned this. Joel Spolsky had this famous "lecture": You Suck at Excel with Joel Spolsky [1], where the talk about pivot table starts at 50:41 [2]. [1] https://youtu.be/0nbkaYsR94c https://youtu.be/0nbkaYsR94c [2] https://youtu.be/0nbkaYsR94c?t=3041 https://youtu.be/0nbkaYsR94c?t=3041

chrisfinazzo 3y ago
If you've never really grokked spreadsheets like Excel, this is a great place to start. Although I don't have Office installed, most of what Joel demos can be done in Numbers if you are so inclined. It's not nearly as powerful as Excel, but for my needs it's capable enough. As regards Pivot Tables, I think Numbers had them at some point early in its history, but it wasn't 1.0 - Notably, Apple's implementation was significantly different than Excel and people complained, loudly enough that it was rewritten to be more compatible with MS in a later version. The elephant in the room is (unsurprisingly) SPSS, which I had the unfortunate luck to encounter in my Research Methods class during grad school. Since that time (2010-2012), many of the other frameworks and tools have emerged to either fill in gaps or do the kinds of analysis that even Excel isn't well suited for.

paulbares 3y ago

I found it too cumbersome, limited and not very intuitive to do it in SQL with the PIVOT operator so that I created my own API to be able to build a pivot table from a simple SQL statement https://github.com/squashql/squashql-showcase/blob/main/TUTORIAL.md#pivot-table https://github.com/squashql/squashql-showcase/blob/main/TUTO.... Roughly speaking, it consists in a single SQL statement and a list of headers for the rows and columns.

TheRealDunkirk 3y ago

I once wrote a time-and-attendance app for a small company I worked for (using Rails 2.x). I put an export function in it, so that the office manager could dump the week's hours, pull it into Excel, and run pivots on person-time-per-project (to pay employees) and project-time-per-customer (to bill customers). After a few weeks, it was clear that the office manager wasn't "getting" how to do this, despite a couple of explanations, so I removed the export, and simply generated both pivot tables in a page. This solved the payment/billing problem so well, that, last I heard, they were still using this application 8 years after I left.

zubairq 3y ago

I feel really stupid as I've been a programmer for 40 years and never used a pivot table. Am I the only one?