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
Henry, this is an Excel issue. Have a look at my answer to Suzanne's comment above (September 11, 2013). The comment describes how to change the Conditional Formatting. If you just want to get rid of it, you can simply delete the CF and it should work right away.
1 reply
Henry, Question 1: Open the Name Manager (Formula Tab | Name Manager), select myChecklist, change the cell range in the "Refers To" input box, click Close and confirm with Yes. Question 2: This is possible, but it would require some considerable changes in the code, since we would have to distinguish between 3 hierarchy levels instead of only 2. Not impossible, of course, but some VBA coding work necessary. I will put this on my list of ideas and maybe I will publish an update some later day including the various suggestions posted in the comment section already. No promise, though.
1 reply
Aiswarya, you are absolutely right: the technique does not work anymore with Office 2013. The root cause: Microsoft disabled scriptable ActiveX controls like the Webbrowser in Office 2013. This has nothing to do with Tableau. It is a Microsoft issue. None of the workbooks I published here with scriptable ActiveX controls (like the Webbrowser or the Slider) work anymore in Office 2013. I can't tell for sure regarding LiveWeb, but I guess LiveWeb is using the Webbrowser, too. As far as I know, the only option of using scriptable ActiveX elements in Office 2013 is to embed them in a VBA userform. I am sorry, I do not see another solution.
1 reply
Rachel, I do not have a Mac and Office for Mac available, so I can't tell you whether this technique works at all on a Mac. I do not see why it shouldn't, but I can't test and tell for sure. I am sorry.
1 reply
Image
A sneak preview of my entries for Tableau’s “Viz as Art” Contest Continue reading
Posted Aug 13, 2014 at Clearly and Simply
Gilbert, the function should be very straight forward to use. First copy the function of my previous comment into the module. Then wrap up the code which adds an Outlook task into an IF clause calling the function, like this: For Each myTask In ActiveSelection.tasks If TaskExists(myTask) = False Then [… the existing code …] End If Next myTask
1 reply
Gilbert, the following function checks if the task already exists and returns true, if it does and false otherwise, i.e. you should only add the task if this function returns false. Function TaskExists (myTask As Task) As Boolean Dim objOLApp As Object Dim objTasks As Object Dim objTask As Object Dim blnExists As Boolean Set objOLApp = GetObject(, "Outlook.Application") Set objTasks = objOLApp.GetNamespace("MAPI").GetDefaultFolder(13).Items blnExists = False For Each objTask In objTasks If objTask.Subject = myTask.Name & " (Project Task)" Then blnExists = True Exit For End If Next TaskExists = blnExists End Function
1 reply
Gilbert, as you said, this code is working, but it just adds a lot of information to the body of the task. It is not handling the requirements you made in your previous comments. If you want to follow my recommendation and create 2 tasks in outlook to get 2 reminders for start and finish date, you have to duplicate the code to add 2 tasks to Outlook. Set start and end date of the Outlook task to the Project task's start date for the first one and to the finish date for the second one. Furthermore, you set the reminder time to myTask.Start – 2 for the first one and to reminder time to my-Task.Finish – 2 for the second one.
1 reply
Gilbert, yes this is possible. You should first make sure the task in Outlook gets a unique name, e.g. a combination of project filename, WBS and the name of the task. You can then loop through all tasks in Outlook to check if the task already exists in Outlook and add it only if it doesn't. A For Each loop, an IF clause and a Boolean variable should do the trick.
1 reply
Gilbert, yes, but the weekly reminder would be just one of the issues. Let me try to clarify with two examples: Example 1: Let's say you have a Project task starting on Monday 08/11/2014 and a duration of 5 days, i.e. the task ends Friday 08/15/2014. If you set the recurrence to weekly, you will never get a reminder for the finish date. Example 2: Now let's say you have the same start date, but this time the task has a duration of 8 working days. You will get a reminder at the start, another one a week later (Monday 08/18/2914), but no reminder at the finish date (Wednesday 08/20/2014). The only way to get around the described issues in the examples would be setting the recurrence to daily, not weekly. However, in my humble opinion, receiving a reminder every day would be more annoying than helpful, especially for tasks with a long duration. Furthermore: I do not see the advantage of your approach compared to my suggestion of simply creating 2 tasks in Outlook.
1 reply
Gilbert, I do not think you will be able to get what you want with a recurring Outlook task. If the duration of the Project task is less than a week, Outlook will not remind you at the finish date. If the duration of the Project task is 2 weeks or longer, it will remind you every week, not only at the start and finish date. I would still recommend to split each Project task into two Outlook tasks, one for the start date and one for the end date (see my previous comment).
1 reply
Gilbert, sorry, I misunderstood what you meant by recurring. You can set the reminder time of a task using the properties .ReminderSet and .ReminderTime, but I do not think it is possible to set two reminders for one Outlook task, i.e. one for the start date and one for the finish date. I do not see a way how to do this in Outlook manually either: you can only set one reminder, i.e. each Outlook task has maximum one reminder. The only way I see to get what you want is to create two Outlook tasks for each Project task: one for the start date and one for the finish date. You could then set the reminders for those two tasks referring to the start and finish date of the Project task.
1 reply
aalok, I can reproduce this. I double checked and the two workbooks I described at the end of the post (displaying files in a folder and displaying PDF documents) do not work anymore. Not at all, I mean not even in Tableau Desktop, let alone on the server. The workbooks linking to a webpage are still working like a charm. I have no clue why. I assume Tableau changed something in the recent versions which affected this functionality of the webpage object. I can't tell you what happened. I am sorry. Maybe you want to search on the Tableau Forums or contact Tableau's support team to get an answer.
1 reply
Gilbert, if you insert a recurring task in Project, a parent task is created with children tasks for each occurrence. Just like any other task, the recurring task has a start and finish date. With the task property .Recurring you can check whether a task is a recurring task or not. If it is, .OutlineChildren.Count gives you the number of children tasks, i.e. how often the task has to be repeated. With this information, you should be able to create a recurring task in Outlook using Outlook's RecurringPattern object and its properties.
1 reply
Gilbert, as far as I know, there is no such thing as a Project task without a start and end date. It is possible to insert a task into Project without specifying start date, end date and duration. You can identify those tasks by the question mark in the task mode column, indicating that Project needs more information. However, even if you do not specify this information, the tasks have a start and an end date: the Project's start date. I would assume the past date you are talking about isn't arbitrary, it rather is the overall start date of your project. If you want to set the dates in Outlook to "None" for the tasks which need more information, you could use the task properties .IsStartValid and .isFinishValid. You can wrap up the code which sets the start and due date in Outlook into an IF-clause like this: If myTask.IsStartValid Then .StartDate = myTask.Start End If If myTask.IsFinishValid Then .DueDate = myTask.Finish End If
1 reply
David, I am sorry, I do not have a Mac available, so I can't test this. I would assume it should work with Excel on a Mac, too, but I can't guarantee. You could send me your workbook by email. I would then check if the problem exists on my Windows machine and - if so - try to solve it. If you can't share your workbook with me, please run the code again, click on "Debug" in the error message and tell me at which line the code stops.
1 reply
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].
Toggle Commented Jul 30, 2014 on Dynamic Sorting with Tableau at Clearly and Simply
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?
Toggle Commented Jul 30, 2014 on Dynamic Sorting with Tableau at Clearly and Simply
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