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
Image
How to create Cartograms in Microsoft Excel Continue reading
Posted 2 days ago at Clearly and Simply
Hitika, the checkboxes are no "real" checkboxes. The cells contain the letter "R" or "£" and are formatted with font type Wingdings2. With this font type an "R" looks like a checked box and "£" looks like an unchecked box. If you are changing the Conditional Formatting of the columns with the "checkboxes", you have to make sure the Conditional Formatting rule formats the cell with font type Wingdings2 (instead of e.g. Automatic). If you want to print only the rows with checked boxes, you would need an extra VBA sub. This sub would loop through the list, hide all rows with unchecked boxes, print the sheet and then unhide the rows again.
1 reply
Rusty, good to hear you got it working. HLOOKUP, VLOOKUP, OFFSET, INDEX. There is always more than one way to skin the cat. I am preferring INDEX to fetch values from cell ranges, because it usually is the fastest option, but this doesn’t matter in your case. "And where are you teaching classes?" Only here. I am doing onsite trainings in Germany from time to time, but I guess this won't help you. No videos or even a comprehensive online course. I am sorry. If you are looking for a good online Excel academy, I am recommending Chandoo's Excel School: Chandoo's Excel School And, for the more advanced user, Daniel Ferry's Excel Hero Academy: Excel Hero Academy Both are well worth the money. If you are interested, please have a look. Please be advised that I am not an affiliate and I do not get money for these recommendations.
1 reply
Rusty, I think you can do this without changing the VBA code: To use different threshold ranges depending on the selected measure, I would recommend to define the ranges somewhere on the worksheet control and then use INDEX formulas to get the currently selected threshold range in the first column of the defined name myMapValueToColor based on the currently selected measure (i.e. the value in cell C15). If the measures have different number formats, you have to adjust the formulas creating the legend texts (D19:D28 on the control worksheet) based on the currently selected measure (e.g. by using a CHOOSE formula). Finally, if the different ranges have a different numbers of bins, you would have to change the named range myMapValueToColor. It now refers to a fixed cell range (B19:C28). If the measures have different numbers of bins, you would replace this range reference by an OFFSET formula based on the number of bins of the currently selected measure. I hope this will be helpful.
1 reply
Nik, I forgot to mention: Regarding my first idea described above, i.e. resizing the cloud: what you could do is to create the cloud, copy it, insert it as a picture (e.g. PNG file), delete the group of textboxes and then resize the PNG image to the desired size. All this does not require too much extra code and shouldn't be a problem in terms of performance. This way, the font sizes would be adjusted by resizing, but you would lose the tooltip feature.
1 reply
Nik, my implementation does not manage the size of the outcome. The data is sorted descending by word count and the algorithm only considers the first n words (n = the user-defined maximum of words). The code then mindlessly adjusts the font sizes of the words according to their values and inserts them in the word cloud if possible, i.e. if they fit into the defined rectangle shpWordCloud. If a word does not fit into this rectangle, it will simply be skipped. Depending on the size of the rectangle, the number of words and the defined minimum and maximum font sizes, it can either be that words are skipped or that a lot of real estate is unused, i.e. the word cloud is much smaller than the defined rectangle. If I understand your requirement correctly, you want to make sure that the final word cloud has the same size as the defined rectangle shpWordCloud, right? I have thought about this during the development of the code, too, but this is way more complex than it may sound. The first idea which came to my mind was taking out the parts of the code checking whether the inserted word fits into the rectangle (and thereby make sure all words are inserted), group all words and resize the group to the size of the rectangle. This sounds easy to implement, but there is a major problem: resizing a group of textboxes does not adjust the font sizes of the texts, i.e. the size of the word cloud would then fit to the size of the rectangle, but there would either be a lot of empty space between the words (if you increased the size of the cloud) or a lot of overlaps (if you decreased the size). The challenge is an optimization problem: you would not use user-defined, fixed font sizes anymore, but rather enhance the code to let the algorithm find the optimal maximum and minimum font size in order to get to a word cloud fitting perfectly into the rectangle. That's tricky, because you only know the exact size of a word after you inserted it. I.e. the algorithm would have to run in several iterations until it finds the optimal maximum and minimum font size. This is possible, but it needs quite some extra coding and it would seriously hit the performance of the algorithm. I am sorry, but I do not see a silver bullet here.
1 reply
Javed, URL actions in Tableau are just passing links (URLs) to the Web Object (i.e. embedded browser) on the dashboard. You can use dimensions or measures from your data and / or parameter values to create a dynamic URL in a Calculated Field and pass this field to the Web Object in the URL action, like I did in the examples 2, 4, 5 and 6 above.
1 reply
Nick, unfortunately, no. As far as I know there is no fix. Microsoft does not ship the Web Components Library anymore. You could try to download and install it separately (see my reply to Sg above), but no guarantee that this will work in your environment.
1 reply
Image
The Cartogram Data Generator: a free Excel Tool to create your own polygon data for plotting Cartograms in Tableau Continue reading
Posted Jun 20, 2015 at Clearly and Simply
Vineeth, I just tried and it works fine for me. It sounds strange that it isn't possible in Desktop but works on Public. Maybe a security setting or firewall rule on your computer?
1 reply
Jory, if you have Tableau Desktop installed, you can download the Tableau workbook and extract the data from it. If not, have a look at this post: Combine Tables and Charts on Excel Dashboards
1 reply
J, no, this is not possible. The web page object is simply an embedded browser showing the website of the link specified in the URL action.
1 reply
Tommy, the easiest way would be to do the highlighting of the important points directly in Excel before exporting it as a picture to PowerPoint. If this is not an option, you have to embed the Excel workbook in the PowerPoint presentation instead of inserting the picture. With an embedded Excel workbook, the recipients can do the highlighting directly in PowerPoint by editing the embedded Excel workbook.
1 reply
Derek, you are right, saving large Excel files as binary Excel files usually reduces the size considerably. However, this is not the case if the huge file size comes from the inserted images and the camera objects. Saving the file with the original size of the flags (as described above) reduces the file size from 7.92 MB to 7.86 MB and this small reduction comes from the worksheets and data, not from the images and camera objects. They are in the .xlsb with the same file size. It is true, camera objects are highly volatile. I do not know a way of keeping them from refreshing without VBA. With VBA, however, you can use a nice trick shared by Jan Karel Pieterse on Daily Dose of Excel: Performance of linked pictures
1 reply
Sam, it's true, the whole process of ungrouping EMF files can be cumbersome depending on the EMF file you are using. Especially getting the names of the regions assigned to the shapes is usually a big problem, if you are trying to plot a map with a couple of thousand regions (i.e. when assigning the names manually isn't an option anymore). I have a map of the UK by postcodes ready to use. I will send it to you by e-mail in a minute.
Toggle Commented Jun 1, 2015 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Martin, you are right, this is the formula. Many thanks for pointing this out. I updated the post now.
1 reply
Image
Calculated Fields in Tableau for German Public Holidays Continue reading
Posted May 31, 2015 at Clearly and Simply
Image
Calculated Fields in Tableau for US Public Holidays Continue reading
Posted May 30, 2015 at Clearly and Simply
Elliot, I am sorry, but I am not sure which example you are referring to. If you are talking about example #7: since version 8 of Tableau, use case #7 does not work anymore. You can't display the files inside of a folder in a Tableau Web Object. Please see also my answer to aalok above. If you are referring to download links provided on a website displayed in the Tableau web object: the Tableau web object is just an embedded webbrowser, so I assume it depends on the settings of your webbrowser.
1 reply
Srijith, C_SHEET_NUMBER is a public constant (set to 1) defined at the top of the module modChoroplethMap. The statements using Sheets(C_SHEET_NUMBER) thereby always refer to the first sheet in the workbook (the map). If you move the sheet with the map to another position in the workbook (e.g. the data sheet first and then the map), you have to change this constant to 2. The problem here is the following: you can address a worksheet in VBA referring either to the index number of the sheet (as I do) or to the name of the sheet. If you refer to the index number, the user shouldn't move the sheet to another position. If you refer to the name of the sheet, the user shouldn't rename the sheet. So it is the choice between a rock and a hard stone. The problem remains: changes in the workbook (sheet position or sheet name) may require changes in the VBA. Regarding the World Map: actually you do not really need the faster version for a World Map. Tushar's original approach is fast enough for only 200+ countries. Have a look at my reply to Peter on September 4, 2014 above.
1 reply
Image
Interactive Selecting and Highlighting Areas on an Map or an XY Scatter Chart in Microsoft Excel Continue reading
Posted May 27, 2015 at Clearly and Simply
Image
Another technique to create interactive charts in Microsoft Excel using an ActiveX Label Control on top of the chart Continue reading
Posted May 25, 2015 at Clearly and Simply
Maxime, unlike Excel, Tableau does not have a REPT function to repeat texts. However, if the maximum number of repetitions is limited (let's say 5), you can build your own function with a CASE statement and simple string concatenations. Let's say [Text] is the text to repeat and [N] is the parameter defining how often the text shall be repeated. The Calculated Field would look like this: CASE [N] WHEN 1 THEN [Text] WHEN 2 THEN [Text]+[Text] WHEN 3 THEN [Text]+[Text]+[Text] WHEN 4 THEN [Text]+[Text]+[Text]+[Text] WHEN 5 THEN [Text]+[Text]+[Text]+[Text]+[Text] END Agreed, not really an elegant solution, but I do not see another way to do this in a Calculated Field in Tableau.
Toggle Commented May 22, 2015 on String Calculations in Tableau at Clearly and Simply
1 reply
John, it is a bit more complicated in Tableau 8, but it is possible: You have [Count Delimiter] as before; LEN([Name])-LEN(REPLACE([Name],"-","")) You have a second helper CF called [Position second]: IF [Count Delimiter] >= 2 THEN FIND([Name], "-", FIND([Name], "-") + 1) END And another one called [Position third]: IF [Count Delimiter] >= 3 THEN FIND([Name], "-", [Position second]+1) END Then the CFs to be displayed: [First Name]: Left([Name],FIND([Name],"-")-1) [Middle Name]: IF [Count Delimiter] > 2 THEN MID([Name], FIND([Name],"-")+1, [Position second]-FIND([Name],"-")-1) END [Last Name]: IF [Count Delimiter] = 2 THEN MID([Name],FIND([Name],"-")+1,[Position second]-FIND([Name],"-")-1) ELSEIF [Count Delimiter] >= 2 THEN MID([Name], [Position second]+1, [Position third] - [Position second]-1) ELSE RIGHT ([Name], LEN([Name]) - FIND([Name],"-")) END [Namesake]: IF [Count Delimiter] >= 3 THEN MID([Name], [Position third]+1) END I hope this will be helpful.
Toggle Commented May 21, 2015 on String Calculations in Tableau at Clearly and Simply
1 reply
John, I hope you already upgraded to Tableau 9, because since version 9, Tableau provides a FINDNTH function. Let's assume the name field in your database is [Name]. First define a helper Calculated Field called [Count Delimiter]: LEN([Name])-LEN(REPLACE([Name],"-","")) This field helps you identify how many delimiters there are. You do not really need to define this CF, you could also use the formulas within the other CFs, but since you need it more than once, I defined a separate CF. Then create a CF for the first name: Left([Name],FIND([Name],"-")-1) Next extract the second part, i.e. the middle name: IF [Count Delimiter] > 2 THEN MID([Name], FINDNTH([Name],"-",1)+1, FINDNTH([Name],"-",2)-FINDNTH([Name],"-",1)-1) END The formula for the last name is as follows: CASE [Count Delimiter] WHEN 1 THEN RIGHT([Name],LEN([Name])-FIND([Name],"-")) WHEN 2 THEN MID([Name], FIND([Name],"-")+1, FINDNTH([Name],"-",2)-FIND([Name],"-")-1) WHEN 3 THEN MID([Name], FINDNTH([Name],"-",2)+1, FINDNTH([Name],"-",3)-FINDNTH([Name],"-",2)-1) END Finally, the namesake formula: If [Count Delimiter] > 2 THEN MID([Name], FINDNTH([Name],"-",3)+1) END I hope this helps.
Toggle Commented May 21, 2015 on String Calculations in Tableau at Clearly and Simply
1 reply