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
Lynne, I don't think formatting cells as numbers would be a problem, neither in the data sheet, nor in the cross tab sheet. You can also use a dimension containing only numbers (not texts) on rows, columns or in the value area. However, there is a limitation in size. Not so much in size of the input data, but in size of the resulting cross tab. In other words, it depends on how many unique entries the dimension in the row header and the dimension in the column header have. The code uses VBA arrays, which can hold a lot of data, but are still limited to a maximum size of used memory. My guess: your resulting cross tab would become too big to handle for the code (a couple of thousand rows and columns) and thus the cross tab can't be created, at least not with the data structure and algorithm I implemented. The code doesn't stop, because of the error handling (On Error Resume Next). You can comment this line out in the code and see where the code stops and which error message you will receive. I think, it will either stop at the integer counter of the Function Unique Items (i.e. you have more than 32,767 unique entries) or you will receive an overflow error. If it is the integer counter, you can change this variable to type "Long", but I am pretty sure you will then receive an overflow error.
1 reply
Lars, I agree, the concept of Combined Fields is a bit confusing in Tableau. The symbol of a Combined Field in the data window is "Abc", indicating it would be a string. If you drag it to a shelf or a mark, it shows the combination of the 2 dimensions separated by a comma (or another user defined separator), but if you use it in a Calculated Field, the value of the Combined Field is always "true". I think the only way to get what you want is – as you described – a Calculated Field concatenating the 2 dimensions instead of using a Combined Field. Having said that, I do not see why you would need to extract values from a Combined Field. Since it is per definition a combination of dimensions, you can always use those dimensions in your Calculated Fields to get what you want. If you have a Combined Field of State, City and want to e.g. get the first character, you just perform a LEFT on [State].
1 reply
Lars, I am not 100% sure I understand your question, but here are my 2 cents: A set in Tableau is a user defined subset of a dimension. It either returns true (value is in the subset) or false (value is out of the subset). Your Calculated Field will return the ASCII code of the first character of the set: either 116 (ASCII code of "t" for true for all values in the subset) or 102 (ASCII code of "f" for false for all values out of the subset). To get around the "Nested Sorting"-dilemma, a set won't help. What Ross is using in his answer on the Tableau forum you linked to isn't a set, it is a combined field. You can use this in the dynamic sorting approach, too. Simply right click on 2 dimensions in the data window and select Combine Fields. Drag this to the Rows Shelf and set the sort of this combined field to Sort By field [State Sort Measure] (in the example posted in the article). Or am I completely misunderstanding you?
1 reply
Right click on the file and open it with Tableau Public.
1 reply
It is exactly the same procedure with Tableau Public.
1 reply
Alexander, download and open the workbook, go to a worksheet, remove the filter, right click somewhere in the visualization, select View Data, switch to the tab Underlying and click on Export All.
1 reply
Alexander, I am sorry, no. Google says: "Your key […] ensures that Google can contact you about your website/application if necessary." If you want to use Google maps in your workbooks, you should create your own key.
1 reply
Alexander, you are right. If you download the Tableau workbook and click on a data point on the Tableau Map you receive an error message in the web object of the dashboard. It is not a Tableau, it is a Google issue. Apparently Google nowadays requires a mandatory API-key which has to be specified in the URL. This wasn't the case back in 2010, when I published these articles. You can get an API key from Google for free and you would then have to change the Calculated Field URL_link to include this key. Have a look here: Google Maps Embed API Surprisingly the Google map seems to be still working fine on the dashboard published on Tableau Public in the article.
1 reply
Duncan, ungrouping the imported object only converts the EMF into Microsoft Office shapes. The shapes are still grouped then, so you have to repeat the ungrouping until you get to the regions of the map, You also possibly have to delete anything else than the regions, like frames around the map, legends, etc. (depending on what is on the EMF map). If this does not solve your problem, you can send me your EMF by e-mail and I will have a look (e-mail link see the top and the left side of the blog).
1 reply
Michael, I double checked with the SVG file mentioned in the article and it is still working for me. There is a warning message when opening the file saying the file format and extension would not match, but if you confirm opening the file with clicking on "Yes", everything works just fine. However, it always depends on the SVG file you are using. I cannot guarantee the techniques works with every SVG file and even if it generally works, there is no guarantee the file really includes the names of the regions.
1 reply
Stefan, thanks for your comments. You are right, there seems to be no way in Internet Explorer to directly open an Excel workbook with Excel. I remember an option in earlier versions of IE called 'Open files based on content, not file extension' and disabling this solved the problem, but this option isn't available anymore. I wasn't aware of that, so thanks for the hints. Even worse, Internet Explorer changes the file extension during the download from .xlsx, .xlsm, .docx, etc. to .zip. You have to manually rename the file before you can open them with the Microsoft Office program. Having said that, although other browsers do not act up in the same way as IE does, you may still have to change some settings, too. In my installation of Mozilla Firefox, for one, clicking on the download link above opens a window asking whether to open the file or to save it. The default program to open the file is Windows Explorer. So you have to change the settings first, before you can directly open the workbook. My Google Chrome installation automatically downloads the file without asking (at least with the correct file extension), but it does not provide an option to directly open it with Excel. Your advice of simply using Chrome or Firefox is a good advice in any case, no doubt about it. However many people just can't do this, because in some corporate environments the Internet Explorer is the only available browser. In this case there is – as far as I can see - no other way than to right click on the link, save the file to your computer and manually change the file extension.
1 reply
Arya, yes this is possible. First you need an extra column in the checklist to specify whether the item shall be included in the completion rate (True) or not (False). Let's say this new column is left to the last column, i.e. the one with the "check boxes". In the VBE you have to change one line of code of the function CompletionRate: Replace If IsItem(rngCheckList(lngRowCount, 1)) Then by If IsItem(rngCheckList(lngRowCount, 1)) And rngCheckList(lngRowCount, lngColumns - 1) Then This should do the trick.
1 reply
Stefan, I do not think it is a problem of the browser you are using. It rather depends on the settings of the browser. If you configured the browser to open .xlsm files with Microsoft Excel, you will be ok. However, if the browser is set to open them with the Windows Explorer or to ask every time you click on an .xlsm file, the Excel workbook will be opened with the Windows Explorer and you will only see the folders and the XML files. As I said above, the easiest way is to right click on the link, save the workbook to your computer and double click there to open it with Excel.
1 reply
Raishankar, TabGeoHack and ShapetoTab convert shapes files (e.g. ESRI shape files or any other polygon spatial data formats supported by the Geographic Data Abstraction Library) into a Tableau Custom Geocoding (TabGeoHack) or into CSV files with the polygon points to be used in Tableau (ShapeToTab). If you have company specific regions you want to plot on a Tableau Filled Map using TabGeoHack or ShapeToTab, you have to create or organize the according shape files first. Last, but not least: please be advised that – for the time being - TabGeoHack is not working with the latest version of Tableau (8.2.). See also my reply to Eduardo above.
1 reply
Eduardo, it is a version issue. I assume you are working with Tableau 8.2, right? TabGeoHack isn't working with 8.2 anymore: Tableau 8.2 uses a newer version of Firebird and TabGeoHack is not compatible with this Firebird version. Richard is - as far as I know - aware of this problem and will try to fix this as soon as he finds the time to (if the issue can be fixed at all). I'd recommend to subscribe to Richard's VizTalk thread to get the updates.
1 reply
Eric, an Excel 2007/2010/2013 workbook is in fact a zipped folder containing XML and other files. If you click on the download link and select open, it is possible that due to your system settings Windows is opening this zipped folder with the Explorer. That's why you see the XML files. Simply right click on the link, save the file to your computer and open it by double clicking.
1 reply
Fortunato, you have to make sure the named range myData covers all rows of your data. Go to Name Manager and change the "Refers To" of the name myData to cover the entire range of your data.
1 reply
Muralee, Ok, two more questions: 1. When the code stops and you see the yellow highlighted line in the Visual Basic Editor, hover with the mouse over the variable varProjApp. A toolbox should appear. What does it say? 2. Right beneath the IF clause where the code stops, there is the following statement: varProjApp.Visible = False. Copy this line of code, insert it above the IF clause where the code stops and change False to True. Run the code again. When the code stops at the IF clause, is Microsoft Project open?
Toggle Commented Jul 17, 2014 on Bring your tasks in a row at Clearly and Simply
1 reply
Muralee, I double checked on my machine and the code is still working like a charm for me. 3 questions: 1. When you are saying the code breaks, do you mean the code really breaks and goes into debug mode or does a message box appear saying "Connection to Microsoft Project not possible"? 2. If the code really breaks, are you sure it stops at the line with the "IF varProjApp Is Nothing Then" statement or is it stopping one line above? 3. Which error message do you get? I assume the "New MSProject.Application" statement isn't working on your machine and I was wondering why. Sorry, but it is difficult to provide a solution when I can't reproduce the error.
Toggle Commented Jul 16, 2014 on Bring your tasks in a row at Clearly and Simply
1 reply
Dh, have you seen Michael's replies to Andrew's and Kevin's comments above? I think he answers your question there.
1 reply
Gilbert, you have to add a loop to repeat the sending of the task for each resource. First define an integer variable like Dim intcount As Integer Then wrap up the entire code inside the existing For Next loop into another For Next loop: For intCount = 1 To myTask.Resources.Count [...existing code...] Next intCount Inside this loop change myItem.Recipients.Add(myTask.Resources(1).EMailAddress) to myItem.Recipients.Add(myTask.Resources(intCount).EMailAddress) That should do the trick.
1 reply
Daniel, thanks for your comment. You must be really all over the idea of predicting sports data with Excel, aren't you? Well, as mentioned before, I do not. However, I recently saw an interesting Monte Carlo simulation of the FIFA World Cup done by the folks of Best Practice Modeling. I only had a very quick look at the model, but it looks really interesting and very professional. Have a look: BPM - World Cup 2014 - Monte Carlo Simulator
1 reply
Greta, try this: select the cell with the completion rate, press F2 and press Enter to force the UDF to run again. If this does not solve the problem, you can send me your checklist by email and I will have a look.
1 reply
Nathan, yes this is possible. Let's assume [Product Name] wouldn't exist in the example above. All you have to do is to change the Calculated Field [Hierarchy Level 3]. It would look like this: CASE [Hierarchy Chooser] WHEN 'Region ► Customer State ► Zip Code' THEN STR([Zip Code]) WHEN 'Category ► Sub-Category' THEN "" END If you then select the hierarchy with 2 levels, Tableau will still show a third column, but this one would be empty and the view will look like a 2 level hierarchy.
1 reply
Image
Interactive drill up and down geographical hierarchies on a Choropleth Map in Microsoft Excel Continue reading
Posted Jun 22, 2014 at Clearly and Simply