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
Ernest, please ignore my previous comment. I didn't see your second message when posting it. Glad you figured it out.
1 reply
Ernest, I can't reproduce the issue. The sorting works fine for me with negative numbers. All I can think of is this: the code uses the active region to determine the range which has to be sorted. Do you have an entirely empty row somewhere in your table? If this is the case and you double click somewhere above this empty row, all records beneath the empty row will not be included in the sorting. Maybe this is the problem?
1 reply
Marta, I am sorry for this very simple question, but it is hard to tell what is going on without seeing the workbook: are you sure the calculation is valid? Maybe you are mixing aggregated and non-aggregated measures and the Calculated Field is invalid?
1 reply
Ben, have a look at this generic example with 3 data series: Download tooltips on xy scatter charts more data series (42K) Maybe this makes things clearer than lengthy explanations in a comment.
1 reply
Ben, you have to adjust the VBA code: the argument Arg1 of the method .GetChartElement specifies the data series. I am referring to the textbox option now: first you need another data column concatenating the text for the tooltip of the second data series. Next, you have to adjust the VBA code e.g. like this: If lng_Argument1 = 1 Then .TextFrame.Characters.Text = Worksheets("Data").ListObjects("tab_data").ListColumns("Tooltip").DataBodyRange(lng_Argument2, 1) ElseIf lng_Argument1 = 2 Then .TextFrame.Characters.Text = Worksheets("Data").ListObjects("tab_data").ListColumns("Tooltip2").DataBodyRange(lng_Argument2, 1) End If For more than 2 data series, you would certainly use a Select Case statement instead of the IF clause. Doing this for the camera object option would require some other adjustments, but those would be along the same lines.
1 reply
Elsheimer, adding more countries is a bit more complicated than adding more years (because of the map). Here is a brief step-by-step how to add one country: 1. Add the new country name and its values to the tables on sheet [data] 2. Add the new country to all tables on sheet [calcs] which perform calculations for all countries 3. Insert the according formulas for the new country on all the tables on sheet [calcs]. Make sure the formulas refer to the correct ranges (i.e. including the new country) 4. Enlarge the list of countries on the dashboard (column P), adjust the data source of the bar chart and adjust the named range myBarChartRange 5. Add the new country to the data source of the line chart on sheet [charts] 6. Insert the shape of the new country on the dashboard, position it correctly on the map and give the shape a name (e.g. S_NEW) 7. Go to the Selection Pane and drag the new shape name down, in order to make sure the new shape is the last one of the shapes to be colored by the algorithm (i.e. right above S_GBR) 8. If you want to keep the small map (indicating the selected country in blue), go to the map icon sheet and perform steps 6 and 7 for the small map 9. Insert this new shape name in the Choropleth Map table on sheet [calcs] in column E 10. Run the RecreateShapeIndex sub 11. Assign the macro SelectShape to the new shape on the dashboard I didn't test these step-by-step instructions. I rather simply wrote this down from the top of my head, but I think this should do the job.
1 reply
Stephan, I agree, finding a map including the names can be difficult. I do not know a site or data source reliably providing SVG files including the names. I am sorry. That being said, I did a quick search on Wikimedia Commons and found this map: UK Map by Statistical Regions The SVG file seems to have most of the county names in column id5. As far as I can see, some of the names are missing, but this map could be a starting point and you could manually add the missing names after you imported the EMF file into Excel. I am not sure the structure of this map fits to the data source you linked to, though.
1 reply
David, you can use a Calculated Field to manipulate the words: LOWER([Words]) will transfer all characters (including the first one) into lowercase letters, e.g. "Night" and "NIGHT" would become "night". UPPER([Words]) will transfer "Night" and "night" into "NIGHT". UPPER(Left([Words],1))+LOWER(MID([Words],2)) will transfer "night" and "NIGHT" into "Night". You would then drag this Calculated Field to the Text Card of the Marks Shelf instead of the dimension [Words] from your source data. Hope this helps.
Toggle Commented Mar 16, 2017 on Word Clouds with Tableau at Clearly and Simply
1 reply
Flavio, I already sent you an email with another workbook.
1 reply
zav, have a look at this follow up post: Microsoft Excel Check List Compilation The article provides 14 variations of the basic checklist, including templates with more than one check column.
1 reply
Flavio, I am not sure I understand your question correctly, but if you have polygon data that form more than one shape and want to plot it on an XY scatter chart, you need more than one data series in your XY scatter chart. Simply add one data series per polygon data series which form one consistent shape and Excel will do the rest. If you want to create freeform shapes from your polygon data, you can directly use the workbook I provided for download. No changes necessary.
1 reply
Mikhail, this is possible with some extra coding. I just sent you an email with the solution.
1 reply
Paul, please refer to my reply to Julie's comment on September 16, 2009 at 6:20 PM.
Toggle Commented Feb 28, 2017 on Bring your tasks in a row at Clearly and Simply
1 reply
Arun, I will send you the workbook in a minute. PS: I edited your comment and took out your personal information to avoid spam in your inbox and unwanted calls. I hope I acted in your best interest.
1 reply
Bish, please have a look at my reply to Paul (third comment from top).
1 reply
Amish, I can’t reproduce your issue. If I have a task with a Finish Date 2/23/2017 and a % Complete of less than 100%, the task is marked as Late (as it should) if today is 2/24/2017 or later. If the task starts on Thu, 2/23/2017 and has a duration of 3 days, i.e. a Finish Date of Mo, 2/27/2017, today (Sa, 2/25/2017), the Status will depend on the % Complete: if the task has a % Complete of less than 67%, the task is Late, if the % Complete equals or is higher than 67%, the task is On Schedule, because there is still one day of three days left to complete it and only one third or less of the work has to be done. This works like it should from my point of view. What is not updated by Project is indeed the filter you may have applied. Let’s say you filtered all tasks with the Status Late and then change the % Complete of one task in a way which changes its Status from Late to let’s say Complete, this task will still be visible, i.e. the filter will not be updated automatically. You have to reapply the filter to get to the list of all late tasks.
Toggle Commented Feb 25, 2017 on Bring your tasks in a row at Clearly and Simply
1 reply
Arun, I just noticed that TypePad is not providing me with the email addresses of my commenters anymore. Can you send me a short email (click on the "Email Me" link at the top of the blog) and I will reply with the workbook from 2007 to 2017. Thanks.
1 reply
Arun, I will send you a version for 2007 to 2017 by email in a few minutes.
1 reply
Amish, Project stores the current status of a task as a number, not as the text that is displayed in Project: On Schedule is 1, Late is 2, Future Task is 3, etc. Have a look here: PjStatusType Enumeration The VBA code imports what is stored in Project and writes it to the sheet, i.e. the numbers, not the text. You either have to change the code to transfer the number to the according text or you create an extra column on the worksheet with a formula assigning the correct text to the number.
Toggle Commented Feb 21, 2017 on Bring your tasks in a row at Clearly and Simply
1 reply
Andrew, unfortunately no, at least not directly. The technique used above takes advantage of the chart method .GetChartElement to identify the data point the mouse cursor is currently hovered over. The Choropleth Maps consist of freeform shapes and there is no built-in VBA method to directly identify the shape under the mouse. I.e. you would have to develop your own VBA code to implement such a method. I never tried, but I assume this may be way more complicated than it sounds. Another option is displaying a customized tooltip after clicking on a shape of the map (e.g. using Application.Caller), but this wouldn't be a real tooltip anymore… Finally, you can show tooltips on a Choropleth Map using a hyperlink as shown in the article Fast Choropleth Map with Enhanced Features but there are no options to customize the tooltip.
1 reply
Arun, in the example workbook provided above, there is a named range called myCurrentValue (cell C10 on worksheet control). The VBA routines IncreaseCounter and DecreaseCounter increase or decrease this value with a delay in milliseconds passed to these procedures by the parameter lngWait.The VBA routines Forward, FastForward, Backward, FastBackward, ForwardToEnd and BackToStart then call one of these two routines to in- or decrease myCurrentValue as desired. These routines are assigned to to the play buttons on the dashboard and will be executed if the user clicks on one of the buttons. VBA increases or decreases the value of myCurrentValue and all calculations in the workbook are based on this value. I hope this sheds some more light on the technique.
1 reply
Anusha, the technique also works for tables with data entered via a data validation list and/or formulas in columns. Thus, your data set up cannot be the root of the problem. I can think of two possible issues: 1. The range name myData does not cover your entire range, i.e. you added rows at the end and/or columns to the right, but did not adjust the definition of myData. In this case, adjust the named range myData to cover the entire range where your data is. 2. You are not using a simple data range, but a table (list object). In this case, you would have to change the code to refer to a list object instead of a simple range. The code always runs to the end, because all errors are skipped due to the On Error Resume Next statement. If this does not solve the problem, you can send me your workbook by email and I will have a look.
1 reply
Nicki, my apologies for this late reply. What you have in mind is possible in VBA, of course. The current implementation simply differentiates between topics and items using two Boolean functions (IsItem and IsTopic). The functions check whether there is a separator (a period) in the first column of the list and if there is one, it is an item, otherwise it is a topic. For your purposes, you would have to change considerable parts of the code and not only check if there is a separator, but rather count the separators to decide which level has been checked. You can build upon the existing implementation and implement the necessary changes, but I assume this will require quite some extra coding and testing. Unfortunately, I do not have the time to do this for you. However, the code is open (no password protection), so maybe you can find a VBA expert in your office or a freelance Excel developer to help you with the implementation.
1 reply
David, the technique is possible on chart sheets, too. I am only working with embedded charts, so I do not have the code for a chart sheet available. However, I remember Ajay over at databison published a workbook with tooltips on a chart sheet a couple of years ago: http://www.databison.com/interactive-chart-in-vba-using-mouse-move-event/
1 reply
Vimal, can you post your workbook somewhere for download? It is easier to understand your issue / question with an example workbook.
Toggle Commented Feb 3, 2017 on Dynamic Sorting with Tableau at Clearly and Simply
1 reply