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
Roy, I am not sure I understand your question correctly, but if you select "Show Header" for the hierarchy level dimensions on the Columns Shelf and hide the field labels for columns, the table should look as you probably want it to. If I misunderstood your question: can you post an example workbook somewhere? I will then have a look as soon as I have the chance to.
1 reply
Joe, this is the Excel workbook I used: Download 6_famous_paintings.xlsx (20052.7K)
1 reply
Joe, you can take any of them. Right click on the link and save the text file to your computer. Open Excel, import the downloaded text file (specify space as the separator), delete the first 6 rows, the last row and the first column and you have your data.
1 reply
Joe, yes, this is the site I downloaded the data from. See also the link provided in the section "The Data Source" of the article.
1 reply
Joe, here you go: 1. Organize the data, e.g. from the website mentioned in Data Source section of the article above: a simple table with two columns containing the X and Y positions of the dots of the painting and – as in my example – a third columns specifying the painting the data set belongs to 2. Open Tableau and connect to the data downloaded in step 1 3. Drag the "Painting" dimension to the filter shelf and select one painting. 4. Show the filter card and select "Single Value" and deactivate "Show All Values" in the Customize submenu 5. Drag X to the Columns Shelf and Y to the Rows Shelf 6. Right click on the X pill on the Columns Shelf and select "Dimension" 7. Right click on the Y pill on the Rows Shelf and select "Dimension" 8. Select Circle as the Mark type 9. Adjust color and size of the circles 10. Adjust the width of the view
1 reply
Richard, the error number indicates that your Excel options are set to disable all ActiveX controls without notification. Change this setting in the Trust Center to enable ActiveX controls and try again.
1 reply
Naveen, sorry for the late reply. Can you elaborate a little bit about why you are not able to sort the data based on the measure values? Or even better, can you post an example workbook somewhere so that I can have a look? Thanks.
Toggle Commented Jan 5, 2017 on Dynamic Sorting with Tableau at Clearly and Simply
1 reply
Dan, sure, send me your workbook by email (see email me link at the top of the blog) and I will see if I can help.
1 reply
sohaib, the workbook is available for download in the section "The Download Link" of the post. In case you already noticed this and had problems with the download: Microsoft Excel files are in fact zipped folders containing XML and other files. Depending on the settings of your system, it may well be that Windows tries to open the file as a zipped folder with Windows Explorer and then you only see the XML files. Simply right click on the link, select Save As and save the file to your computer. If you are using Microsofts's Internet Explorer, you also have to change the file extension from .zip back to .xlsm and you should then be able to open the file with Microsoft Excel by simply double clicking.
1 reply
Stefan, if you did not change anything in the code, it should work, even if products have different amounts of rows and columns. Check it out in the example workbook I posted for download. E.g. set the impact to "High" and the Probability to "High" for all risks of one Risk type on the Risk List sheet and select this Risk Type by using the filter drop down. The Pivot Table will then have only one row header and one column header. If this isn't the case in your workbook, I would assume something is wrong with the named range "myPivotTable". The code clears everything in this range first and then recreates the whole thing. I assume you do not have defined this name in your workbook and that's why the initial "cleaning part" of the code does not work and the old stuff remains on the sheet. If this is not the root cause of your issue, you can send me your workbook and I will have a look.
1 reply
Thomas, I gave that a try, too, but it didn't work for me. It takes only a few clicks to set up the query, but I never managed to get the entire job done. Loading the data took forever and always stopped with a "ran out of memory" error after a couple of thousand records. Anyway, thanks for the suggestion.
1 reply
Henk, sure: 1. in the worksheet object model: in the IF clause beneath the SELECT statement, delete "Or Target.Value = C_NA" from the IF condition and add it to the ELSEIF condition 2. in the sub ChangeTopicStatus of the module, add "Or varData(lngRowCount, lngActiveCol) = C_NA" to the condition of the third (the innermost) IF clause This should do the job.
1 reply
Mike, this is possible (of course) with a few extra lines of code. Have a look at this workbook: Download check_list_log_file.xlsm (35.9K) It is based on the original check list and writes all changes to a log sheet. To keep it simple, it is coming with one disadvantage, though: if the user double clicks on a topic, only the change of the topic status is logged, not the (automatic) changes of all its items. You could enhance the code to improve this (if you want), but I think this workbook should give you a jump start. Hope this helps.
1 reply
Enrico, you are absolutely right, this was actually a bug in the workbook I originally posted in my reply to Brian. It was simple to fix, though. I only forgot to adjust the second parameter passed to the UDF StatusCompletionRate. I fixed this now. Please download the workbook again and it should work. Thanks for pointing this out.
1 reply
Enrico, have a look at my reply to Brian's comment on October 10th, 2016 above. In my answer to Brian, I included a download link to a workbook which provides what you are asking for.
1 reply
Sophie, it should work if the plot area of the chart and the image have exactly the same size and the same top and left corner. Having said that, it is indeed very cumbersome to align the two objects. Before I published this post, I was trying to do this automatically every time the workbook was opened or the sheet was activated. I wrote this code snippet: Sub Align_PlotArea_and_Image() With ActiveSheet.ChartObjects("Chart1") ActiveSheet.Shapes("Image1").Width = _ .Chart.PlotArea.InsideWidth ActiveSheet.Shapes("Image1").Height = _ .Chart.PlotArea.InsideHeight ActiveSheet.Shapes("Image1").Top = _ .Top + .Chart.PlotArea.InsideTop ActiveSheet.Shapes("Image1").Left = _ .Left + .Chart.PlotArea.InsideLeft End With End Sub It works ok for the size of the image, but for some reason (which I do not understand), it does not work for the top and left position. However, after you ran the sub, you are already pretty close and can do the rest manually by repositioning the image. Hope this helps.
1 reply
Jon, first group the shapes (e.g. S_USA and S_CAN) and give that group a new, unique name, e.g. S_NA. Then insert a data row somewhere inside the data table and assign the name D_NA to the according cell of this new row in column K. Finally go to the control sheet, insert a row somewhere in the range which assigns the names to the shapes and assign S_NA to D_NA. If you then run the algorithm (by selecitng another measure), USA and Canada will be colored according to the data you inserted in the data row added in step 2. Hope this helps.
Toggle Commented Nov 18, 2016 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Sophie, I recently noticed a problem with the ActiveX label control, too. Even if the background of the label is set to transparent, it seems to be opaque after you turned off the design mode. The technique used to work well, but it doesn’t anymore. I have no clue why, probably a new bug of the label control which came with one of the latest Microsoft Office updates. My suggestion: you can use an image instead of a label control. The ActiveX image still seems to work fine, but there is no guarantee this will last forever. You would have to replace the label control by an image control and change the VBA accordingly to address the image instead of the label. Hope this helps a little bit.
1 reply
Mike, there actually may be some smaller distortions during the procedure of ungrouping an EMF file to freeform shapes, but I can't reproduce what you are describing. I just opened the World Map in Excel 2016 and there is no noticeable gap between USA and Mexico. If your version of Excel distorts the map, you may want to create your own version from scratch following the instructions e.g. in this article: Build your own Choropleth Maps with Excel
Toggle Commented Nov 9, 2016 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Sue, have a look at this thread on the Tableau Community Forum: https://community.tableau.com/message/180164 I think Richard Leeke is answering your question there and he is kind enough to even provide an example workbook with a step-by-step explanation.
1 reply
Ajay, let me start with one important point for clarification: if you want to use the code in all of your workbooks, you either have to copy the code to all the workbooks, define the name "myData" (manually or by VBA code, see below) and save them as macro enabled workbooks or you have to transfer the code to an add-in (probably using a new class module providing the Worksheet_BeforeDoubleClick event). As per your question how to programmatically add and delete a name: the following line adds the name "myData" referring to the used range of the first worksheet: ThisWorkbook.Names.Add Name:="myData", RefersTo:=Worksheets(1).UsedRange The following line deletes the name "myData": ThisWorkbook.Names("myData").Delete I hope this helps.
1 reply
Terri, sure, send me your workbook by email (see Email Me-link at the top of the blog) and I will have a look as soon as possible.
1 reply
Sue, if you want to concatenate all texts of one dimension separated by a line break, you have to create the following calculated field: PREVIOUS_VALUE("") + CHAR(10) + ATTR([Text]) [Text] is the dimension containing the individual texts. The last record of this calculated field will contain all texts in the database separated by a line break. I hope this helps.
Toggle Commented Oct 28, 2016 on String Calculations in Tableau at Clearly and Simply
1 reply
Diletta, if you can't find Italy's polygon data for download on the web, you can download ESRI Shape Files e.g. from here: http://www.gadm.org/country and use Richard Leeke's ShapetoTab tool, described at the end of this article: Create Your Own Filled Maps in Tableau ShaptToTab transfers the shape files to the polygon data and provides CSV files you can then directly copy into Excel.
Toggle Commented Oct 27, 2016 on Cartograms in Microsoft Excel at Clearly and Simply
1 reply
Onditi, unfortunately: no, I have never done anything in the field of sports results prediction. And even if I would: don't get me wrong, but if I would have a model at hand reliably predicting the results of future football matches, I don't think I would share it for free.
1 reply