Exporting data for analysis

[topfive...@gmail.com]

Does anybody export data for analysis in as spreadsheet and if so what is your process.  I would love if i was able to visualize my productivity over time because bringing gamification to MLO, as in spreadsheet graphs, would be huge...for me.

Cheers.

[Steve Kunkel]

I haven't really been using this functionality, but to set up a re-usable template, I'd recommend something like this one I made about 4 years ago.  If it works as I intended, you can export your outline as Excel XML.   Then open my MLOsheet here.  Open the tab MLOraw, and do Select-All, Delete.  Now, open your fresh export and do Select All, Copy.  Go immediately to MLOraw, click on cell A1, then paste (Ctrl+v).   

The reason this works (hopefully it did).  Is because the filter table references this raw data.  If you click in FilterTable, C3, you'll see "=MLOraw!A12."   MLOraw!A12  is Always where the MLO Excel export puts the top/left most corner of the table.  

Hopefully that brief explanation made sense.  I can expand, if needed.  

IMPORTANT:  This is a one-way process.  You can't use Excel to update your outline.  Also, your tables, graphs, etc can only read, rearrange, regurgitate the data from the raw sheet.  You should never actually change data this way--unless it's via dumping in a fresh export to the MLOraw sheet.  Update your data in MLO, then occasionally do a bulk export. 

note: I just realized the FilterTable is filtered by column G, "Start time."  You can unfilter, as needed.  Also if I recall correctly, the "Optional Label" for the GanttChartt doesn't work correctly...  I got frustrated with it back in 2017 and never finished it.

[Steve Kunkel]

As I re-read my post, I see more explanation is needed...  When I say that I recommend this setup, I don't mean that you should necessarily use a table or a gantt chart.  I just mean leave your raw export data as raw data.  REFERENCE those cells in other sheets, the use those other sheets for your data analysis.    A person might be tempted to convert their export directly into an Excel Table.  If you do that though, you'd have to re-build the Table each time you did an export.  The setup I have here allows you to have a "re-usable" template.  

also note:  The sheet "CulledRows" is merely a "helper sheet" that feeds into the gantt chart.  It was intended to be hidden.  

[Steve Kunkel]

Eh...  Okay, so when I made this back then, I monitored my exports over the course of a year or two, to make sure that the format never changed (e.g. that cell A12 always the top of the table and the columns are always in exactly the same locations.)   That is a must for this type of re-usable template.  I just checked an export from today though, and some columns have indeed changed.  Cell A12 is still the top left, but "Text Tag" was inserted as column 7.  That pushes all the subsequent columns over one.  Sorry.   I've run into this problem with other database export templates I have.  It is possible to adjust the spreadsheet, but it is probably easier to just build a new one from scratch...  :- /   

also note that "Places" is now "Contexts."

2017 Is completed 2021 Is completed
2017 Task name 2021 Task name
2017 Start time 2021 Start time
2017 Due time 2021 Due time
2017 Recurrence 2021 Recurrence
2017 Contexts 2021 Places
2017 Text tag 2021 Flag
2017 Flag 2021 Starred
2017 Starred 2021 Notes
2017 Notes 2021 Outlined task name
2017 Outlined task name 2021 Full path
2017 Full path 2021 Created
2017 Created 2021 Modified
2017 Modified 2021 Completed
2017 Completed 2021 Importance
2017 Importance 2021 Urgency
2017 Urgency 2021 Goal
2017 Goal 2021 Project (immediate)
2017 Project (immediate) 2021 Project (top level)
2017 Project (top level) 2021 Is project
2017 Is project 2021 Project Status 
2017 Project Status  2021 Project Completion %
2017 Project Completion % 2021 Task Effort
2017 Task Effort 2021 Min time required
2017 Min time required 2021 Max time required
2017 Max time required 2021 Hide in To-Do
2017 Hide in To-Do 2021 Complete subtasks in order
2017 Complete subtasks in order 2021 Has subtasks
2017 Has subtasks 2021 Has uncompleted subtasks
2017 Has uncompleted subtasks 2021 Priority By Importance
2017 Priority By Importance 2021 Priority By Urgency
2017 Priority By Urgency 2021 Priority By Both

2017 Priority By Both

[topfive...@gmail.com]

Thank so much for this response.  It was exactly what I was looking for.  Also, thank you for the details of the process.

Cheers.