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 a customizable S-shaped Function in Tableau Continue reading
Posted 9 hours ago at Clearly and Simply
Todd, yes, this is possible, but you need some considerable enhancements of the workbook: 1. Make 2 copies of the existing map on worksheet [World Map] and resize / position them as you like 2. Make sure all shapes have unique names, i.e. instead of the shape S_USA, you would then have e.g. S_USA_1 on map 1, S_USA_2 on map 2 and S_USA_3 on map 3 3. Triplicate the drop down (combo box) and assign a target cell to them on sheet [Control] 4. Triplicate the data table on sheet [Data], i.e. one data table for each map 5. Adjust the named ranges along the same lines as already done with the shapes, e.g. instead of D_USA, you would then have D_USA_1, D_USA_2 and D_USA_3 6. Make sure the INDEX formulas in "Selection" column of the data tables use the correct target cell of the corresponding drop down 7. On the [Control] worksheet you then need 3 named ranges for MapValueToColor (MapValueToColor_1, MapValueToColor_2, MapValuetoColor_3) and 3 named ranges for MapNamedToShape 8. Adjust the Minimum and Maximum formulas right to the new named ranges for MapValueToColor to refer to the corresponding data ranges 9. Finally go to the VBE and triplicated and adjust the For Next loop in the sub UpdateMap. Replace this part: For Each myCell In Range("MapNameToShape").Columns(1).Cells CheckColor Range(myCell.Value), "MapNameToShape", "MapValueToColor" Next myCell by this: For Each myCell In Range("MapNameToShape_1").Columns(1).Cells CheckColor Range(myCell.Value), "MapNameToShape_1", "MapValueToColor_1" Next myCell For Each myCell In Range("MapNameToShape_2").Columns(1).Cells CheckColor Range(myCell.Value), "MapNameToShape_2", "MapValueToColor_2" Next myCell For Each myCell In Range("MapNameToShape_3").Columns(1).Cells CheckColor Range(myCell.Value), "MapNameToShape_3", "MapValueToColor_3" Next myCell I didn't try, but I think this should do the job.
Toggle Commented Apr 14, 2017 on Choropleth Maps with Excel at Clearly and Simply
1 reply
Kyle, the relationships, i.e. the lines between the dots / pie charts are plotted by setting the Mark Type of [Line Y] to "Line" and by dragging [ID] to the Details Card of the Marks Shelf. The dimension [Relationship] is for informational purposes only (used in the Tooltips). Please have a look at section "Step 4 - Connect the Dots" in the article and the sheet [step 4] in the workbook posted for download.
1 reply
Image
How to calculate sunrise, sunset, solar noon and daylight hours for any location at any given day in Tableau Continue reading
Posted Apr 9, 2017 at Clearly and Simply
James, sure, send me an email (email-link at the top of the blog) and we will figure out how I can help you.
1 reply
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?
Toggle Commented Mar 24, 2017 on Dynamic Sorting with Tableau at Clearly and Simply
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