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
Mohammad, I suppose, my readers are – just like myself – primarily using Microsoft Excel and not Google docs. I would assume, your chances to find someone here transferring my code to Google Apps script are next to nothing. I recommend to publish your request on a Google Apps script forum.
1 reply
Mohammad, maybe my answer wasn’t precise enough. I am sorry. To be crystal clear: there is absolutely no way to make my code working on a Google docs sheet. My code is written in Visual Basic for Applications, the programming language dedicated to write code for Microsoft Office applications like Excel, Access, Word, etc. Google Apps script is a completely different programming language. If you want to have the functionality on a Google docs sheet, you can't use anything of my VBA code in the Excel worksheet provided above, not one single line. You have to write your own Google Apps script code from scratch.
1 reply
Anil, actually the interactive drop lines in the workbook posted for download above work only on charts with only one data series. Having said that, you can enhance the tem-plate by duplicating and adjusting all named formulas and with a few small changes in the VBA code. Have a look at this example with an XY scatter chart and 2 data series: Download Interactive Drop Lines XY Scatter with 2 Series (36K)
1 reply
Mohammad, the features are implemented in VBA (Visual Basic for Applications), Microsoft's programming language for Office applications. As is, they work in Excel only. It may be possible to rebuild the same functionality in Google Docs with Google's Apps Script, but I do not know. I am not familiar with Apps Script.
1 reply
Mohammad, have a look at this file: Download fil-ter_table_by_double_clicking_selected_columns (39K) You can specify in the row above the column headers, which columns shall be viable for filtering by double clicking. If you set the value above a column header to TRUE, you can filter by double clicking, if the cell value is FALSE, the double click feature is not available in this column.
1 reply
Giuseppe, I am sorry, I do not understand your question. What do you mean by "remove control checkbox"? What exactly do you want to remove?
1 reply
Bastin, I do not know of a way to write something to a text file using a standard URL action in Tableau. You may want to have a look at Michael Martins guest posts here Writing and Reading Tableau Views to and from Databases and Text Files – Part 1 and Writing and Reading Tableau Views to and from Databases and Text Files – Part 2 about writing from Tableau to a database or a text file, but this approach requires an R installation. Not sure, if this is applicable in your situation.
1 reply
Bastin, I am not sure I understand your question correctly: do you want to save what the user clicked on in the webbrowser (e.g. in an online form)? If so, I do not think this is possible. As I wrote in my reply to Javed’s question above, the URL action is simply passing a URL to an embedded webbrowser. I do not see how you could get back into Tableau what the user clicked on in the browser, let alone write this to a csv file.
1 reply
Marie, delete or comment out the following part of the code and it should work: If ActiveSheet.AutoFilterMode = False Then rngTable.AutoFilter End If
1 reply
Nirmal, you have to take care of the numbering convention in the first column: the number of the topic followed by a period and the number of the checklist item. The code uses this first column to identify what is a topic and what is an item and which item belongs to which topic.
1 reply
Image
How to provide different options of customized tooltips on a Microsoft Excel chart and let the user decide which one to display Continue reading
Posted Sep 14, 2016 at Clearly and Simply
pj755 IF ISNULL([Date]) THEN "Pending" ELSE STR([Date]) End or (a simpler syntax): IFNULL(STR([Date]),"Pending")
1 reply
Giuseppe, please see the section "How to use this template for your own checklists" at the end of the article.
1 reply
Julian, you are right, using the command prompt, DIR and piping the results into a text file is another option. Thanks for pointing this out. Well, there is always more than one way to skin the cat, right? I am wondering, though, why Microsoft does not provide this option on the Ribbon of the Windows Explorer, but forces us to type in cryptic DOS commands as if we would still live in 1985…
1 reply
Image
How to create a list of all filenames inside a specified folder and its subfolders including additional information like file types, sizes, dates and path lengths in Microsoft Excel Continue reading
Posted Aug 29, 2016 at Clearly and Simply
Damon, please see my reply to Marc in the comment above (August 28, 2014).
1 reply
Image
How to create customizable, meaningful tooltips on XY Scatter Charts in Microsoft Excel Continue reading
Posted Aug 27, 2016 at Clearly and Simply
Hema, sorry, I do not understand the problem. Why should the parameter based custom sorting not work across several worksheets? A parameter is global, i.e. it has one defined value throughout all sheets in the workbook. As long as you have set the Sort by Field correctly on all sheets, the sort order will change on all sheets after you changed the parameter.
Toggle Commented Aug 20, 2016 on Dynamic Sorting with Tableau at Clearly and Simply
1 reply
Kevin, 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 Firefox or Chrome, you can then open the workbook with Excel by double clicking on the file. If you are using Microsoft's Internet Explorer or Edge, however, the IE will change the file extension from .xlsm to .zip during download. Simply change the file extension back to .xlsm.
1 reply
Vinod, I am sorry, but I do not have the time to do the VBA development for you. The workbook and code is open, i.e. not password protected, though. If you do not have the experience in writing VBA code, maybe you can find a colleague of yours who does?
1 reply
Vinod, Requirement A: 1. You can add or delete rows as you like, you just have to take care of the numbering convention in the first column of the checklist and – in case you add items at the end of the checklist – to make sure the named range covers the entire checklist. Please refer to my reply to Evelyn's comment above for a step-by-step. 2. The Completion rate UDFs will always be based on the number of rows in the checklist, so there is no need to change anything here. Requirement B: This needs some extra coding for the additional checkboxes above each column and some changes in the UDFs. Have a look at checklist 12_check_list_select_relevant_items, which allows to decide for each item whether it is relevant and shall be included in the completion rate calculation. You would have to change the completion rate functions of the template 08 along these lines.
1 reply
karthik, you have to adjust the left position of the vertical line according to the date where you want it to be. If – for instance – the vertical line shall be at today, you would do a calculation like this: (cnstTableWidth / _ ((Month(datProjectFinish) - _ Month(datProjectStart) + 1) * 30)) * _ (Date - datProjectStart + 1) Then add the offset constants which define the left of the Gantt chart and use the result as the left position of your vertical line.
1 reply
Jake, this post was published in 2011, the other one even back in 2010. The technique with the simple URL-link worked fine back then. Nowadays Google requires a mandatory API-key which has to be specified in the URL. You can get an API key from Google for free and you would then have to change URL-links in the data as described here: Google Maps Embed API
1 reply
James, yes, I would only change the alias to e.g. "Post_Code_5": required_geocoding_fields: name (String): alias: Post_Code_5 unique_id: true If you can't get it working, you can send me a download link to your shape files (here in a comment or by email) and I will give it a try.
1 reply
James, ah, ok, I didn't know that. If Tableau does not recognize 5 digit postcodes of the UK, you will probably indeed have to either work with TabGeoHack or with the polygon map approach. I looked at the –-info output you provided in one of your previous comments and it seems to me as if "name (String)" is the name of the field in the shape file which contains the names of the postcodes, because –-info says name (String) = AB10 1 so you have to specify "name (String)" as the required_geocoding_field in your YML file.
1 reply