This is Robert's Typepad Profile.
Join Typepad and start following Robert's activity
Join Now!
Already a member? Sign In
Robert
Munich, Germany
Interests: new media, optimization, strategy, forecasting, business intelligence, project management, financial planning, business plans, telecommunications, revenue management, business performance management, simulation, pricing, intelligent data analysis, business cases, revenue assurance, cable operator, operations research
Recent Activity
JT, the technique is still working with Tableau 9.2. It is not a version issue. If the Calculated Field is not working in your workbook, I'd assume there is a syntax problem in your formula. Maybe the quotation marks are wrong, e.g. you are using smart quotes instead of straight ones?
1 reply
Mo, I do not think a PivotTable will get you what you want. You could either use PowerQuery or a VBA routine to read the data from the sheets and write it into one table of a new worksheet. Combining data from several sheets into one is a very common requirement and you can find a lot of freely available VBA code snippets on the internet.
1 reply
courtviz, I am afraid, no, this is not possible. The web browser object is just an embedded Internet Explorer and you can’t open a locally stored Tableau workbook with a browser… Even worse, the entire technique described above does not work anymore since Office 2013: Microsoft disabled scriptable ActiveX controls in Excel worksheets and PowerPoint slides in Office 2013 and 2016 (unless you make the registry tweak mentioned in one of the comments above). But maybe embedding your Tableau workbook could be an alternative for you. Have a look at the follow up article: Embed and Open Tableau Workbooks in PowerPoint
1 reply
RK Lata, the VBA code makes the shape "Zoom Area" visible only during the selection process, i.e. after you clicked on the map and while you are spanning the zoom area. After you clicked again, the code makes the shape invisible again. You can rename the shape in the Selection Pane as described in my previous comment even if it is invisible.
1 reply
RK Lata, you can either select the shape and rename it in the name box (the little box at top left above the worksheet) or open the Selection Pane (ALT-F10), select the shape (click on it twice) and rename it there.
1 reply
Unfortunately, you can't. The functionality of double clicking to check and uncheck the checkboxes is implemented in Visual Basic for Applications, the programming language coming with Microsoft Office. I do not see a way to transfer this VBA code to a webpage. You can certainly create a similar functionality on a webpage using HTML, but you can't use the code provided in the workbook above.
1 reply
Version 7.0
1 reply
John, checklist #9 has several status columns and time stamp columns. Use template #9 as the master, have a look at the code in #5 to see how a user stamp is added, add user stamp columns to the worksheet and add the necessary code from template #5 to the code of template #9.
1 reply
EB, I can reproduce the problem. It is not a size error, it is a character set issue. The extraction stops at the image of Rüppel's Fox, the first shape with a "Umlaut" in the name. The GetNodeData function returns the string "Ru¨ppel's-Fox-_-Vulpes-rueppellii" as the name of this shape and this causes the issue. The CreateTextFile method of the object can handle this, but apparently the VBA command Kill can't. The code fails when trying to delete the text file and exits the sub. That’s why the other shapes are not extracted. The work-around is simple: In the Excel tool, go to the VBE and in the sub CreateandImportCustomShapes replace On Error GoTo ExitSub by On Error Resume Next If you run the algorithm again, all shapes will be extracted. Only downside: the text file for Rüppel's Fox will not be deleted and you have to delete it manually before you copy the images to your Tableau repository.
1 reply
Srikanth, the article above describes step by step how to create Tableau filled maps from ESRI shape files. Did you try to follow this tutorial? If so, did you encounter an issue or roadblock? At which step and what exactly is the issue?
1 reply
Julian, I appreciate your comment, but unfortunately I can't really reply to your feedback, because I did not develop the online Tableau Custom Shape Extractor. I only created the little Excel tool described above which extracts shapes from a Tableau workbook. I first thought of recommending to post your comment on the Tableau Public Blog: All Your Shape are Belong to Us but it seems as if the comments are deactivated there. Thus, I'd suggest you directly contact Tableau to provide your feedback.
1 reply
Sean, sure this is possible, but it requires some changes on the data sheet and of course some additional coding. First you would insert a data structure on the worksheet [data] to assign the column categories to the groups (e.g. an assignment table) and give the range a name you can refer to in the code. Next you would write additional code to define the width of the group boxes based on the category values belonging to each group, insert text boxes of this width at the position you want to have them and finally assign the group names to the text of the text box. All this is pretty much along the same lines of the other parts of the code. In other words: most of the required additional code to implement your idea can be based on the code that already exists (see e.g. step 8 of the code). The workbook and VBA code is open, so you are welcome to enhance the code for whatever you like.
1 reply
Irina, I am sorry, I do not understand your questions. What do you exactly mean by "represent the percentage of the ranks"? I also do not understand what you mean by "a category having more than 100%". As I wrote in the introduction to the article, a Marimekko Chart is a combination of a 100% stacked column chart and a 100% stacked bar chart combined in one view. I do not see how a category could have more than 100%.
1 reply
Peter, I do not know if this is only a typo in your comment, but the correct line of code should read Worksheets(1) instead of Worksheet(1). Maybe you unintentionally deleted the s?
1 reply
Juan, you can also add countries, but you have to 1. enhance the list of countries on the worksheet control and add the name(s) 2. enhance the calculation range on worksheet calculation and add the names and formulas 3. make sure the data sources of the chart are covering the correct ranges on worksheet control As long as you take care of this, you do not have to change anything in the VBA code.
1 reply
Juan, you also have to change the names of the countries on the sheets calculation and control.
1 reply
Juan, the workbook is open, so you can put in any data you like. Just have a look at the data and the calculation worksheet and see how the data is structured and what you may have to change.
1 reply
Isabella, since version 2007, Microsoft Excel files are actually zipped folders including XML and other files. If your Internet browser opens Windows Explorer when clicking on the download link, right click on the link instead and select "Save Target As" to download. If you are using Microsoft's Internet Explorer, the IE will change the file extension from .xlsm to .zip during download. Simply change the file extension back to .xlsm and you can open the workbook with Excel by double clicking on the file.
1 reply
imyuinny, I never tested the workbook with Excel for Mac, but I do not see why the code shouldn't work on a Mac (provided you enabled macros, of course).
1 reply
Jeroen, the app looks promising, but – as far as I am concerned - I am never using add-ins or apps providing interactive functionality. I am developing models for other people and forcing my clients to install add-ins before they can use my workbooks isn't an option. I am using add-ins, of course, but only to increase my productivity, like ASAP utilities or Power Utility Pak. I never use add-in functionality as an integral part of my models. Having said that, I am sure many people will find your hint very useful and interesting, so many thanks for your comment. Much appreciated.
1 reply
Cristian, I never used Qgis, so I unfortunately I can’t help you here. I am sorry.
1 reply
Bobby, first of all, you are obviously using the simpler code published in previous posts. I do not know how many regions your map has, but I would strongly recommend the faster algorithm provided in the article above. As for your question: if you place a timer inside your code, the code has to run all the time. That's why I recommended to use an event driven procedure. The Worksheet_Change sub of a worksheet is fired every time something changes on this sheet and this seems ideal for your purposes. Simply put this procedure into the worksheet object in VBA (Microsoft Excel Objects | Tabelle02 (data)): Private Sub Worksheet_Change(ByVal Target As Range) UpdateMap End Sub I do not know in which way you are updating your data, but this doesn't really matter. As soon as something changes on the worksheet data, the sub will be executed and the map will be updated.
1 reply
Bobby, you could e.g. call the Choropleth Map sub in the Worksheet_Change event sub of the data worksheet. Every time the worksheet changes (if new data comes in), the procedure is fired and the map gets updated.
1 reply
Sumit, the code imports all data of the checklist into a VBA array and writes the entire list back to the sheet. The code doesn't change the numbering. If the numbering in the first columns changes, I would assume this is caused by the number format of the cell. If you insert a text like "1.1", Excel tries to convert this into a date. Maybe this is the root cause of your problem. To avoid this, simply format the first column as Text and it should work. Having said that, I do not see how this could be caused by the completion rate, because the completion rate UDF does not change anything on the sheet (except for the result of the completion rate).
1 reply
Widya, there is a simpler way: create the names in the cell range right to the cells with the data by concatenating "D_" and the name of the regions. You can then use the Excel feature "Create from Selection" in the "Defined Names" section of the Formula tab to assign these texts as names to the cell range where the data is. Afterwards you can delete the formulas.
1 reply