tag:blogger.com,1999:blog-85931870110189112092024-03-14T18:24:16.841+00:00Ταμπλώ τζιαι μέσαGuerrilla techniques for data analysis and mapping. Any opinions are my own and not my current or former employer'sStelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.comBlogger67125tag:blogger.com,1999:blog-8593187011018911209.post-62510659519793121692019-09-08T10:29:00.000+01:002019-09-08T13:37:35.387+01:00Ofcom's media nations report: Bar chart epic fail<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
As a designer of interactive visualisations you have great power and great responsibility to stop the user from doing stupid things. Whoever put <a href="https://www.ofcom.org.uk/research-and-data/tv-radio-and-on-demand/media-nations-2019/media-nations-2019-interactive-report">Ofcom's media nations </a>report together on Power BI, left the option to the user to tick plotting the responses to multiple questions, with the percentages going well above 100%. I'm not familiar enough with Power BI to know if this is the fault of the software rather than the designer.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4MuorV9YiXXdMvI0rgeHuqSxUhsHHds62fbiFv__faHZvT5pmhHqNb6TWqxh-ZfDnoTTN6hRETEp80xcxxKTzYOVSfP0NZr6EySD66Q6QEdpqRakA9VK-IMaGUBfHgARQx61SkBotzG8/s1600/103percent.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="785" data-original-width="1322" height="379" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4MuorV9YiXXdMvI0rgeHuqSxUhsHHds62fbiFv__faHZvT5pmhHqNb6TWqxh-ZfDnoTTN6hRETEp80xcxxKTzYOVSfP0NZr6EySD66Q6QEdpqRakA9VK-IMaGUBfHgARQx61SkBotzG8/s640/103percent.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
And the magic setting producing the magic plot (one of the questions only has answers for 2018-2019<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjguI0XjnJfSz6zie73T00Y_VxlrcTcVHrhWc4fgYIaUhViHToknkG67GKQ2XIrSPQ5szz1MyFWw8Z6-ZKEPcVLo0PU-8IYZAZbpwgpOSErsCxMA-P_GbWRw9rKrimojjffxAKspK2_idc/s1600/multiple.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="569" data-original-width="1022" height="356" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjguI0XjnJfSz6zie73T00Y_VxlrcTcVHrhWc4fgYIaUhViHToknkG67GKQ2XIrSPQ5szz1MyFWw8Z6-ZKEPcVLo0PU-8IYZAZbpwgpOSErsCxMA-P_GbWRw9rKrimojjffxAKspK2_idc/s640/multiple.png" width="640" /></a></div>
<br /></div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-1138775206166091762019-08-14T22:51:00.001+01:002019-08-14T22:51:35.706+01:00Distinct count of countries VS count of Countries<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCk36lLfHS5UmJnKNdgDX3lgRzff-v34h2An5Wv7D-BtdxwX-aR8daACvw3SycFTCls7_JGOMnqxdHo1M8_MrddvH3XGSdoQFLUhUEKImmdgYWHh7pwaIj91lQZFAv-9bY0N80fqso_yk/s1600/countd_korea.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="456" data-original-width="1054" height="275" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCk36lLfHS5UmJnKNdgDX3lgRzff-v34h2An5Wv7D-BtdxwX-aR8daACvw3SycFTCls7_JGOMnqxdHo1M8_MrddvH3XGSdoQFLUhUEKImmdgYWHh7pwaIj91lQZFAv-9bY0N80fqso_yk/s640/countd_korea.png" width="640" /></a></div>
So was it 35 countries or 17 countries after all? It's<a href="https://tableautziaimesa.blogspot.com/2017/03/pie-chart-epic-fail.html"> the second time I'm complaining about the Institute of Engineering and Technology</a> and<a href="https://eandt.theiet.org/content/articles/2019/08/un-says-north-korea-committed-cyber-attacks-on-35-countries/"> their approach to data analysis</a>.</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-85991299515432368532019-08-11T12:07:00.000+01:002019-08-11T12:07:07.960+01:00A sneak preview of the second Tableau Server Users Group meetup in London<div dir="ltr" style="text-align: left;" trbidi="on">
Last month was the <a href="https://www.meetup.com/Lets-talk-Data/events/262612755/">second London Tableau server users group meetup</a>. Jonathan MacDonald went through a long list of tips and tricks for server users and administrators, a few I knew already but also an awful lot that I didn't.<br />
<br />
I won't go through all of them, as I wasn't writing them down, or snapping a pic for every single one. I did take the following two pics though. The first trick is that you can append filters to the URL:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpM4_YXT3Pot65d7kJPkaPymItNJhobKjKnBR41AGJ1akQMDwb1y6YOEMfcXgVtCK_IEWJCBv9aAipr0zS7i6lBlJSm9gg9ZzS9Fquc8qiaJ-jFvYPrGMg2BctBWrv9XA45EJ-FHP4X5o/s1600/20190725_185020.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1018" data-original-width="1600" height="404" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjpM4_YXT3Pot65d7kJPkaPymItNJhobKjKnBR41AGJ1akQMDwb1y6YOEMfcXgVtCK_IEWJCBv9aAipr0zS7i6lBlJSm9gg9ZzS9Fquc8qiaJ-jFvYPrGMg2BctBWrv9XA45EJ-FHP4X5o/s640/20190725_185020.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
and the second one is that you can access an xml containing the server status again through a URL</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-7_vl92KKpbf5Pwe3_hyBW8t5Vy2jwQAoRR4DNd0U1OzlvYOpLRE7R91ucgI3SclarzvC25oI2XTUX7wpblepmUXP4Fyktgi3S-NSSu_Am8t10dLRUiTcYOK3wZVjYGoryJYm_MzCdbc/s1600/20190725_193344.jpg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1072" data-original-width="1600" height="427" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi-7_vl92KKpbf5Pwe3_hyBW8t5Vy2jwQAoRR4DNd0U1OzlvYOpLRE7R91ucgI3SclarzvC25oI2XTUX7wpblepmUXP4Fyktgi3S-NSSu_Am8t10dLRUiTcYOK3wZVjYGoryJYm_MzCdbc/s640/20190725_193344.jpg" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-18674146579311576932019-08-09T21:53:00.000+01:002019-08-09T21:53:31.398+01:00Extracting the list of coordinates for geographic role 'airport' out of Tableau<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
Don't they say a picture is a thousand words? All you have to do is navigate to </div>
<blockquote class="tr_bq" style="clear: both; text-align: center;">
C:\Program Files\Tableau\Tableau Public 2019.2\local\data</blockquote>
(modify appropriately for Tableau desktop). Copy the airport tableau source and the GEOCODING database into another folder, drag and drop the tds on tableau and voila, you have the airports and their coordinates! You can even export them to csv for general purpose use.<br />
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsJ_8o1aWL0WeSTFvDWM-rV3SYCIcYfVPFSlD0NjZ1v95q50F9-2vxsS5eGDY8MNpMC-jETDo5gH8APri6wf1w6Io5tsY6LAtD2YVDNQ3_fPfgHgpVJjah2QtJ3v2KHYSG17ZQgMBInYo/s1600/airports.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="309" data-original-width="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsJ_8o1aWL0WeSTFvDWM-rV3SYCIcYfVPFSlD0NjZ1v95q50F9-2vxsS5eGDY8MNpMC-jETDo5gH8APri6wf1w6Io5tsY6LAtD2YVDNQ3_fPfgHgpVJjah2QtJ3v2KHYSG17ZQgMBInYo/s1600/airports.png" /></a></div>
<br /></div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-17455770610895758412019-06-30T11:16:00.000+01:002019-06-30T11:16:00.889+01:00Workarounds for getting new 2019.3 features in earlier versions<div dir="ltr" style="text-align: left;" trbidi="on">
The 2019.3 beta is out and here's the <a href="https://www.tableau.com/products/coming-soon">list of new features</a>. It's nice of Tableau to automate some of the things that could be done with workarounds in previous versions. If you like the new features but are stuck in an old version, or if you are a new kid wanting to know how things were done in ye olden days, follow the links below:<br />
<a href="https://tableautziaimesa.blogspot.com/2018/05/spatial-filtering-by-distance-in-km.html">Distance between two points as a calculation</a><br />
<a href="https://tableautziaimesa.blogspot.com/2018/01/converting-uk-national-grid-to-latitude.html">Working with data in UK national grid projected coordinates</a> (haven't tried this script in TabPy to see how slow it would be, worked fine as a standalone python script for data preprocessing)<br />
<a href="https://interworks.com/blog/tladd/2013/08/22/automated-pdf-email-distribution-tableau-views-using-powershell-and-tabcmd/">Pdf subscriptions</a> (courtesy of Interworks)<br />
<br /></div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-90246453556243726182019-02-17T22:12:00.000+00:002019-02-19T22:01:49.343+00:00Tableau public 2019.1 allows for powerpoint export<div dir="ltr" style="text-align: left;" trbidi="on">
You will have noticed, dear reader, that a lot of the images I use in the blog are screenshots of the workbook while it's being edited in Tableau Public, before publishing. Tableau has spared a thought for those like me, and from the latest version allows me to export to PowerPoint even before publishing to Tableau Public.<br />
<br />
So from this :<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjW7tE6dZ6Vye-9ln0n_kUc8Jl4LaNTUEGBdS8AudSAtdE1oIZxHQGVHzFIIIGSi4_J3hr1O7HWhVrFYdFLw2tE3zadhIsnQZErG7yYLFp7-7je3M6WhbUVCbQEHtqPyVsAHMaZ51y8CFI/s1600/export_to_ppt.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="577" data-original-width="1025" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjW7tE6dZ6Vye-9ln0n_kUc8Jl4LaNTUEGBdS8AudSAtdE1oIZxHQGVHzFIIIGSi4_J3hr1O7HWhVrFYdFLw2tE3zadhIsnQZErG7yYLFp7-7je3M6WhbUVCbQEHtqPyVsAHMaZ51y8CFI/s640/export_to_ppt.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
we go to this:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLIDe9gkz6g12El5-HQ692TW4HiwcyRitxibhvT3Mw_NWQhZR_TTcn9p1h33U2Mxcc-mOED4Y9lLR59cHo4JVhxEUVBunpC45n_Ysl8QO6-6Wqz8SEqQOuF3ARDz8dyPjTuE8Uppa3FZA/s1600/exported_ppt.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="686" data-original-width="807" height="544" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhLIDe9gkz6g12El5-HQ692TW4HiwcyRitxibhvT3Mw_NWQhZR_TTcn9p1h33U2Mxcc-mOED4Y9lLR59cHo4JVhxEUVBunpC45n_Ysl8QO6-6Wqz8SEqQOuF3ARDz8dyPjTuE8Uppa3FZA/s640/exported_ppt.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
It is basically a powerpoint with the image exports of the sheets, but still quite handy! As a general rule when I want to put Tableau visualisations into powerpoint I observe two rules:</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<ol>
<li>Export the view and legend(s) separately into separate png files, so that the legend does not become diminutively small</li>
<li>When resizing the inserted png file, make sure I keep the aspect ratio of the original.</li>
</ol>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-42667591587998224792018-12-25T21:12:00.001+00:002018-12-25T21:13:06.377+00:00Managing the monthly Flow allocation using a scheduled ftp file grab<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
Running a flow for every new file in the FTP repository is eating away my monthly allowance</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGkSJkOMBucEGFw0kQjvalyNAA8P8txME6aaz7XUEQwaAUpudn7oq7suwRXOcY_AX_Zeb1iBfLrgr3e1gd0h-rKY9pekAXM4dTBYZalv1fzaS-8BKIvaMWttgnUxKUET2x73XjK0OpSZU/s1600/approaching+limit.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="332" data-original-width="403" height="263" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGkSJkOMBucEGFw0kQjvalyNAA8P8txME6aaz7XUEQwaAUpudn7oq7suwRXOcY_AX_Zeb1iBfLrgr3e1gd0h-rKY9pekAXM4dTBYZalv1fzaS-8BKIvaMWttgnUxKUET2x73XjK0OpSZU/s320/approaching+limit.png" width="320" /></a></div>
Therefore since I'm not interested in real time monitoring, a more efficient approach is to only fetch files once a day. Note that the ftp directory list gives a body on which we can run a 'foreach' loop, to then only select the wanted files whose filenames satisfy the condition.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidL5CDXVwB5Gnj1Xx0CqjClhL6CdmDLbTIAXMxZIaroDQR_hF3VVwr26JSdxktoJAH_m2_Rzf5w5kWI0g-cuhGjNeDA0Ez1jxAZfGLGces9_Wp2SFCb9dP1kWI4S_RL61VgZVmJjSDhOY/s1600/ftp_flow_sustainable.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="818" data-original-width="1306" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEidL5CDXVwB5Gnj1Xx0CqjClhL6CdmDLbTIAXMxZIaroDQR_hF3VVwr26JSdxktoJAH_m2_Rzf5w5kWI0g-cuhGjNeDA0Ez1jxAZfGLGces9_Wp2SFCb9dP1kWI4S_RL61VgZVmJjSDhOY/s640/ftp_flow_sustainable.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
If you got this far, Merry Xmas!!!</div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-19680216587624815192018-12-23T20:22:00.000+00:002018-12-24T18:36:49.600+00:00FTP fetching and maintaining filename with Microsoft Flow<div dir="ltr" style="text-align: left;" trbidi="on">
In previous posts I looked at <a href="https://tableautziaimesa.blogspot.com/2018/07/microsoft-flow-as-last-resort-in-place.html">grabbing web content with microsoft flow on a scheduled trigger</a>, and <a href="https://tableautziaimesa.blogspot.com/2018/12/conditionals-in-microsoft-flow-for-rss.html">triggering similar website grabs from RSS feeds</a>. A more old fashioned scenario involves downloading files from an FTP server. In that case the content already exists in the form of a file and has a filename, so there is no need to 'construct' a unique filename, we can simply name it in the same way that it is named on the server. On the other hand, this can be seen as an advantage over traditional ftp clients in that we can actually rename our files based on our own convention rather than be forced to keep the original filenames.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsOcOAY9fWG_jjCZMcMfjaBVoSYn2LgWisAjwNzlgj1kEiSL3Sdo8Jc7MvO0yTkvtqfuzoZmwp1YvTApRQaM89lU134PZ2wzcIKJ_RyJKMKm32yiNdA1QTzqT5cTWj5QxAQv7FdBQGQGY/s1600/ftp_flow.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="421" data-original-width="636" height="420" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsOcOAY9fWG_jjCZMcMfjaBVoSYn2LgWisAjwNzlgj1kEiSL3Sdo8Jc7MvO0yTkvtqfuzoZmwp1YvTApRQaM89lU134PZ2wzcIKJ_RyJKMKm32yiNdA1QTzqT5cTWj5QxAQv7FdBQGQGY/s640/ftp_flow.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
Finally, having changed my mind about downloading all files and wanting to introduce a condition, I find that <a href="https://tableautziaimesa.blogspot.com/2018/12/conditionals-in-microsoft-flow-for-rss.html">I was wrong in my previous post</a>, I can drag and drop the create file component inside the 'yes' outcome of the condition.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMPUVFY6RNPhqOBl9KzOtvTmkqy5F9lYFI5KXwwuHFT7sScqigzvGGLC32FsAehkwJIM64DKPIpx_FyQ8AOkKjuqPWHcbeCRQZKM8JMsiymblZsfiwePEy5CRS2nalyW281v2DJFsXRXo/s1600/drag+n+drop.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="619" data-original-width="1261" height="313" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgMPUVFY6RNPhqOBl9KzOtvTmkqy5F9lYFI5KXwwuHFT7sScqigzvGGLC32FsAehkwJIM64DKPIpx_FyQ8AOkKjuqPWHcbeCRQZKM8JMsiymblZsfiwePEy5CRS2nalyW281v2DJFsXRXo/s640/drag+n+drop.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<br /></div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-25910780892510130012018-12-17T21:55:00.002+00:002018-12-18T22:46:22.857+00:00Operational Intelligence basics: Looking for data loss with a scheduled data feed<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
It is worrying how many people, from the novice visualisation enthusiast to the experienced data scientist, just assume all is well with the underlying dataset and go on to visualise it, feed it through algorithms etc. </div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
My first use of Flow was to capture DX cluster data. I was requesting an XML every few hours, which contained 500 records. Now the <a href="https://clublog.freshdesk.com/support/solutions/articles/53364-dxlite">websource </a>is designed for near real time monitoring, not for people like me to download a complete archive. So sometimes 500 records do not go far back enough to avoid having a gap with the previous fetch, as is shown in the morning of the 9th December below. Colouring by the filename (effectively the date and time of fetch) helpfully shows that the gap corresponds to a colour change, and very possibly means there was data loss. On the other hand the gap on the 12th December happens 'within' a particular colour band, i.e. in the middle of a fetched file. So it is probably a genuine lack of activity rather than data loss. I have subsequently changed my flow to do more frequent fetches. </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ZrMZEfmXYJoYPxiKlz3L7xGv-kvHz1yl2QHQOmdnvE3NChZxVHXGbJO4WkH6scA1cpUzTXWKot42eYD3XLU3TqO0pTn1Sm_aIRyBNULrDQ-PZuwlkG4bJLTpwKu30-dz7UI2DCjOaDk/s1600/skip.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="364" data-original-width="1076" height="212" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0ZrMZEfmXYJoYPxiKlz3L7xGv-kvHz1yl2QHQOmdnvE3NChZxVHXGbJO4WkH6scA1cpUzTXWKot42eYD3XLU3TqO0pTn1Sm_aIRyBNULrDQ-PZuwlkG4bJLTpwKu30-dz7UI2DCjOaDk/s640/skip.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
</div>
But what about the actual lack of activity? We look in a bit more detail by adding frequency in 10s f kHz on the vertical axis and being a bit more careful about accuracy of the thickness on the horizontal axis using the calculated field constant 1/(24*60) for the minute.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQr9KD757izZY_PdFUvdh5TQxq2o5KhC3stzIa0fRj51HgnAIWcsuGcsXjntlJzlYCzg4B1H7aEBRhP8r6BgP6GgchOVfKixXZg8tTAaLbViZTINJQuDuROVdug2r5YQEsEEODpw4QQl4/s1600/gantt.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="570" data-original-width="1221" height="298" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQr9KD757izZY_PdFUvdh5TQxq2o5KhC3stzIa0fRj51HgnAIWcsuGcsXjntlJzlYCzg4B1H7aEBRhP8r6BgP6GgchOVfKixXZg8tTAaLbViZTINJQuDuROVdug2r5YQEsEEODpw4QQl4/s640/gantt.png" width="640" /></a></div>
Now compare this slow dying down and picking up again to a data gap below:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhanyVgPgeYaIWalJOCrbTMXDbwVED9ZqXTSskeMLY8C1sxNRcua_k1YL4UcfAn1oHa3NoY9KKlfDOvc7ISrUFdI_3_eNyFGuP_T44BssP0d6yrVlrI-NwfR3ZETce_375eP8nTrBAjRqY/s1600/gantt_skip.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="604" data-original-width="1224" height="313" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhanyVgPgeYaIWalJOCrbTMXDbwVED9ZqXTSskeMLY8C1sxNRcua_k1YL4UcfAn1oHa3NoY9KKlfDOvc7ISrUFdI_3_eNyFGuP_T44BssP0d6yrVlrI-NwfR3ZETce_375eP8nTrBAjRqY/s640/gantt_skip.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-64617067083318019522018-12-16T19:58:00.001+00:002018-12-16T19:59:40.393+00:00Conditionals in Microsoft Flow for RSS feed processing<div dir="ltr" style="text-align: left;" trbidi="on">
For the Flow aficionados amongst you, check out the Flow online conference videos on YouTube.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="separator" style="clear: both; text-align: center;">
<iframe allowfullscreen="" class="YOUTUBE-iframe-video" data-thumbnail-src="https://i.ytimg.com/vi/cY0HG52qovQ/0.jpg" frameborder="0" height="266" src="https://www.youtube.com/embed/cY0HG52qovQ?feature=player_embedded" width="320"></iframe> </div>
<div class="separator" style="clear: both; text-align: center;">
I've referred to the <a href="https://tableautziaimesa.blogspot.com/2017/02/the-story-of-pig.html">golden age of web 2.0 in Yahoo!</a> in a previous post. One of the cool products of that time was <a href="https://en.wikipedia.org/wiki/Yahoo!_Pipes">Yahoo! Pipes</a> that provided effectively pipework for RSS feeds. </div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
With similar applications in mind, Microsoft flow can be a worthy replacement. I needed to only retrieve the web page linked from an RSS feed if the title contains a particular keyword. See the screenshot below. The flow web editor does not allow for dragging and dropping an action into the alternative paths following the conditional. I guess this will be a possibility when <a href="https://docs.microsoft.com/en-us/business-applications-release-notes/october18/microsoft-flow/design-flows-in-visio">editing Flows in Visio</a> becomes available. This might look like a peculiar combination to those of us who think of Visio as a tool for drawing diagrams, but Visio already has Sharepoint workflow functionality, and Microsoft Flow is to replace Sharepoint workflows as you'll hear in the conference videos.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4NckJtmo_qUimbw_TBVDbUGLv9V6TBIczI9UH5dT120wkkQu_xmrlpXO5tn-sk0ONCnDVvzXREZiVu2U5qlIgg-xoRzwbBClYwosytmOWN9kid2fU08O4SSEUt3OZ_-dWrxc88t00h3U/s1600/conditional.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4NckJtmo_qUimbw_TBVDbUGLv9V6TBIczI9UH5dT120wkkQu_xmrlpXO5tn-sk0ONCnDVvzXREZiVu2U5qlIgg-xoRzwbBClYwosytmOWN9kid2fU08O4SSEUt3OZ_-dWrxc88t00h3U/s1600/conditional.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="850" data-original-width="1321" height="411" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4NckJtmo_qUimbw_TBVDbUGLv9V6TBIczI9UH5dT120wkkQu_xmrlpXO5tn-sk0ONCnDVvzXREZiVu2U5qlIgg-xoRzwbBClYwosytmOWN9kid2fU08O4SSEUt3OZ_-dWrxc88t00h3U/s640/conditional.png" width="640" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh4NckJtmo_qUimbw_TBVDbUGLv9V6TBIczI9UH5dT120wkkQu_xmrlpXO5tn-sk0ONCnDVvzXREZiVu2U5qlIgg-xoRzwbBClYwosytmOWN9kid2fU08O4SSEUt3OZ_-dWrxc88t00h3U/s1600/conditional.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><br /></a></div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-80758680628073940612018-12-10T22:07:00.000+00:002018-12-10T22:10:01.678+00:00Using Excel to turn XML format data into a table<div dir="ltr" style="text-align: left;" trbidi="on">
Back in the early noughties when I was trying to learn a bit about web development, XML was all the rage. These days it has been displaced in various applications by json and other formats. Configs, that should have been safe XML territory, are also threatened by json, with <a href="https://tableautziaimesa.blogspot.com/2018/12/fixing-flaws-of-flow.html">Microsoft Flow</a> being an example of such use of json. Even so, a number of legacy uses of XML are still going and it is worth being able to decipher it.<br />
<br />
Of course you could simply open an XML file in an editor and do lots of manual editing to turn it into csv. At the other end of the spectrum, scripting languages are good at parsing this format. I have found the <a href="https://docs.python.org/3.7/library/xml.etree.elementtree.html">Python XML Element Tree library</a> useful for that.<br />
<br />
A middle road exists, which is to simply drag and drop the file on Excel. This functionality is hardly surprising given that <a href="https://en.wikipedia.org/wiki/Office_Open_XML">the excel file format itself is based on XML</a>. Using this example data: <a href="http://dxlite.g7vjr.org/?band=vhf&limit=500&xml=1">http://dxlite.g7vjr.org/?band=vhf&limit=500&xml=1</a><br />
Ignore the style pop up question, then you get a few options:<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKzOQeaCydyfZqy-1Y_7FELMSq0lKg8AxV-Cl6A7KRAD7yGpKXCZ27eBkMX1VHMJTXT5NU8G6xK6Bf4vj9tbq2SRL5e89eQw31y4Mxvur2cF_qsp5tuVne6wFbeN-OiDuIaXO0kYQcwZI/s1600/XML+table.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="458" data-original-width="480" height="380" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKzOQeaCydyfZqy-1Y_7FELMSq0lKg8AxV-Cl6A7KRAD7yGpKXCZ27eBkMX1VHMJTXT5NU8G6xK6Bf4vj9tbq2SRL5e89eQw31y4Mxvur2cF_qsp5tuVne6wFbeN-OiDuIaXO0kYQcwZI/s400/XML+table.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
and finally a warning about schema.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHvSVcD-KfVpH9JEsqN3eZdmFvNCR0tFnsjLrrS_ibnQxQx8xLg5b2Les0JNvOL0w8ylMFRXaS6MOncD_F1LDiPIH8YKFYRpRQFbdLMpga-uhmJET8miwChkp0CoiA05x_c9P2SQRhNCs/s1600/XML+schema.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="121" data-original-width="426" height="90" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhHvSVcD-KfVpH9JEsqN3eZdmFvNCR0tFnsjLrrS_ibnQxQx8xLg5b2Les0JNvOL0w8ylMFRXaS6MOncD_F1LDiPIH8YKFYRpRQFbdLMpga-uhmJET8miwChkp0CoiA05x_c9P2SQRhNCs/s320/XML+schema.png" width="320" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
Sticking with all the defaults results in the desired table in excel</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtuq0QSDuMaZZE33PFYCRkCs-MRFgihQjZCDXpzqwFtGxRYO3lRlzvc7unFJhSNBVTqnFJ9Fw-Ajo_N9ddkhC8mvg60D3G46d6velKjXc7kArs-lPzKg9rpT29v891oLii_KQn-FiigfM/s1600/excel_table.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="401" data-original-width="973" height="260" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjtuq0QSDuMaZZE33PFYCRkCs-MRFgihQjZCDXpzqwFtGxRYO3lRlzvc7unFJhSNBVTqnFJ9Fw-Ajo_N9ddkhC8mvg60D3G46d6velKjXc7kArs-lPzKg9rpT29v891oLii_KQn-FiigfM/s640/excel_table.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
Things of course will be more complicated with more complicated XML data, and it will be worth looking at the file in an editor, and considering using a proper script to convert it. But excel can be a useful quick and dirty solution.</div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-20223102712245327022018-12-08T22:34:00.004+00:002018-12-08T22:34:37.323+00:00Fixing the flaws of Flow<div dir="ltr" style="text-align: left;" trbidi="on">
In a previous post I complained about <a href="https://tableautziaimesa.blogspot.com/2018/08/the-flaws-of-flow-cant-zip-downloaded.html">Flow not being able to compress files</a> on the go. The issue with that was that as my files were not stored locally by default, once I started the archiving process with 7zip it would first cause the download of the file locally, then add it to the archive. Often with a flaky network something would disrupt the download, which would then mess up with the archive. The workaround has been to specify that all the files in that folder are stored locally, thus decoupling the download from the archiving.<br />
<br />
I know, not something to write home about. Let's look at the config of the HTTP component in more detail. Oops! There is an automatic decompression button set to 'On' by default! Make sure it's off unless you really want decompression.<br />
<br />
As I had a few failed runs, I've changed the retry policy, from the exponential default starting with an interval of seconds, to a fixed interval with quite a few minutes interval. I find this works better for a website that has gone temporarily down, especially given that I don't particularly want the flow to be responding in real time. The fact that this can be done from inside the config rather than by adding more steps to the flow is something I learned from the great <a href="https://sergeluca.wordpress.com/">Serge Luca</a> in his Sharepoint Saturday presentation.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEik2ARKapIr9VLUO7ieVtxnxwfBedcGYUIU5I79cvdJ6fQnUPid4yK1AQPYSjWXvyrGcWH3wHCT-wIJ2VNhBZX7cQzAvPmTNoRg8UjDSCDOdaNJOFl53Y28O3v2JdqXFPFqkYQ0Ai8kGkE/s1600/automatic+decompression.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="725" data-original-width="600" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEik2ARKapIr9VLUO7ieVtxnxwfBedcGYUIU5I79cvdJ6fQnUPid4yK1AQPYSjWXvyrGcWH3wHCT-wIJ2VNhBZX7cQzAvPmTNoRg8UjDSCDOdaNJOFl53Y28O3v2JdqXFPFqkYQ0Ai8kGkE/s640/automatic+decompression.png" width="528" /></a></div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-46623134045181137982018-08-24T22:33:00.000+01:002018-08-24T22:33:02.945+01:00Unknown region breaks map in Blogger's Audience stats<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
Blogging is all about vanity in the end, and to flatter ourselves we check on the audience stats provided by Blogger from time to time. Today I notice something rather peculiar: as I fiddle with the time-span (day, week, month) I notice that the maps works fine when it knows the countries in question.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY8jQIxsPFgJL_u3pgalZZJh4hFr-J2S0m4pkR-8AItKJ63ZFNvsrJP8V5lCBU2Rguh-45zv6DrpDar9No3gnJfJIEz37RjS99ZWm6sPen_h219wi_Jky9AMuCYO78kO6Yl9zJQwGDx5c/s1600/usa_russia.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="336" data-original-width="378" height="355" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiY8jQIxsPFgJL_u3pgalZZJh4hFr-J2S0m4pkR-8AItKJ63ZFNvsrJP8V5lCBU2Rguh-45zv6DrpDar9No3gnJfJIEz37RjS99ZWm6sPen_h219wi_Jky9AMuCYO78kO6Yl9zJQwGDx5c/s400/usa_russia.png" width="400" /></a></div>
But lately 'unknown region' has been showing up, and that seems to destroy the map altogether, so no country is coloured anymore. Tableau would have happily warned me of one null while still colouring all other countries.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSjnKvUOibSvDJ8WHuBow4QQgR4Gkyb6knwcfW8QHpuC2tGrZfcdYI5PrevI_6JpjJVU0UTbRFdYdtBBrb-YoZI4aRtMZUC14SKfOEOj61RRyoUE3dOayZDDsXKKF0K3vdKA2aGtJKaFo/s1600/Unknown.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="397" data-original-width="384" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgSjnKvUOibSvDJ8WHuBow4QQgR4Gkyb6knwcfW8QHpuC2tGrZfcdYI5PrevI_6JpjJVU0UTbRFdYdtBBrb-YoZI4aRtMZUC14SKfOEOj61RRyoUE3dOayZDDsXKKF0K3vdKA2aGtJKaFo/s320/Unknown.png" width="309" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-61902121497951719342018-08-18T11:33:00.001+01:002018-12-11T10:00:12.103+00:00The flaws of Flow: Can't zip downloaded files<div dir="ltr" style="text-align: left;" trbidi="on">
Microsoft Flow has been proving more useful in my Office 365 dominated environment. I've been using the FTP connector to download masses of files to my OneDrive. The OneDrive comes with a 1 TB limit, which is plenty but it is worth trying to keep things compact. Unfortunately, while flow can <a href="https://flow.microsoft.com/en-us/blog/auto-unarchive-files/">extract from zipped archives</a>, it <a href="https://powerusers.microsoft.com/t5/Flow-Ideas/Create-a-archive-file-from-files-File-System/idi-p/28347">can't do the opposite</a>. In order to manually create a zipped archive, the computer downloads all the files, zips them and uploads them back to OneDrive. Gotta love Microsoft!</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-33409934937593568652018-07-29T12:18:00.001+01:002018-12-11T09:59:55.510+00:00Microsoft flow as a last resort in place of cron and windows scheduler<div dir="ltr" style="text-align: left;" trbidi="on">
You know the situation: while you are not the biggest fan of MS out there, your employer's IT department are. Of course you don't really have proper access to a Linux system to set up a cronjob to do your data fetching for you, and you don't even have an 'always on' windows machine to be able to do this via Task Scheduler.<br />
<div>
<br /></div>
<div>
Well, you have to make do with what you have, and in this case, that is Office 365, a lesser known component of it called <a href="https://powerusers.microsoft.com/t5/General-Flow-Discussion/hyperlink-to-download-file/td-p/50100">Flow</a>. There is an upside to this to rescue your pride somewhat: while it is a humble office app it uses the<a href="https://flow.microsoft.com/en-us/blog/use-expressions-in-actions/"> same language as azure logic apps</a>, so think of it as cloud training!</div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-55413475543578169512018-07-17T21:46:00.000+01:002018-07-17T21:46:27.651+01:00Using Post code Sector GeoJson from TableauMapping.bi<div dir="ltr" style="text-align: left;" trbidi="on">
Ok, so this time I'm using <a href="https://www.tableaumapping.bi/">https://www.tableaumapping.bi/ </a>properly. I connect using the web connector from tableau Public to https://www.tableaumapping.bi/wdc and chose the Post code Sector table. I then blend in my own data with post code addresses. I use a calculated field to generate the sector from the full post code in my data:<br />
<blockquote class="tr_bq">
<span style="color: #3d85c6;">left</span>(<span style="color: orange;">[postcode]</span>,<span style="color: #3d85c6;">len</span>(<span style="color: orange;">[postcode]</span>)-2) </blockquote>
and voila! a much more detailed map than the one I got before <a href="http://tableautziaimesa.blogspot.com/2018/07/splitting-prefix-out-of-british.html">using only the first half of the postcode</a>.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3yPh31obq9HGRwkQQSIFiOQD8SCX3RWvEvS9CTB94ISYT6jDgKT0JS7Id0hRXkbcW25PLD-FydnKzcbfXg9_sOXqj3mIWak1mbR-ShoMKBCQHEC3Jr7JbpwcCR_ZXUBt41O26NYpSP1E/s1600/post+code+sector.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="712" data-original-width="1055" height="428" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3yPh31obq9HGRwkQQSIFiOQD8SCX3RWvEvS9CTB94ISYT6jDgKT0JS7Id0hRXkbcW25PLD-FydnKzcbfXg9_sOXqj3mIWak1mbR-ShoMKBCQHEC3Jr7JbpwcCR_ZXUBt41O26NYpSP1E/s640/post+code+sector.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-33698561073468933142018-07-17T21:26:00.001+01:002018-07-17T21:26:18.906+01:00Strange maps courtesy of Tableau mapping BI<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both; text-align: center;">
While fooling around with the otherwise worthwhile <a href="https://www.tableaumapping.bi/">https://www.tableaumapping.bi</a> website last night, I managed to get it into a state producing some rather interesting maps. The one below shows the UK ceremonial counties trampling over Iceland (cod wars all over again!). </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSVpdTWamlw66l7mjtGVxos81p9_tnYuRpO92yl-Lj7B2WQbfFsBbLA4_Xr2_Iv4cN2zEBQn3r3APcakVBKBYyScTvtmLZK5UZ_ma1NHxAKs_IoQKxbw0B4sTZ1RWydXhwpR99mRbQBDc/s1600/UK+displaced.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="900" data-original-width="1600" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjSVpdTWamlw66l7mjtGVxos81p9_tnYuRpO92yl-Lj7B2WQbfFsBbLA4_Xr2_Iv4cN2zEBQn3r3APcakVBKBYyScTvtmLZK5UZ_ma1NHxAKs_IoQKxbw0B4sTZ1RWydXhwpR99mRbQBDc/s640/UK+displaced.png" width="640" /></a></div>
Another one shows the Bristol channel housing the whole of the EU and a few fellow travellers broken down to NUTS 2!<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7mui4_YqHtOmWoXIwseH5AhXJ4UNIx-5CHzb18rKF2HSpb1uDoHghpn0oBuphyphenhypheneiAsmD1pNzusSnBIWiP0ybAqBaMqdGPf2dHDuIyNu7QoC1UTqfqYkcp3Dik_LdoI_O5Owl1bMBcv8E/s1600/NUTS2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="900" data-original-width="1600" height="360" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7mui4_YqHtOmWoXIwseH5AhXJ4UNIx-5CHzb18rKF2HSpb1uDoHghpn0oBuphyphenhypheneiAsmD1pNzusSnBIWiP0ybAqBaMqdGPf2dHDuIyNu7QoC1UTqfqYkcp3Dik_LdoI_O5Owl1bMBcv8E/s640/NUTS2.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
I'm having trouble reproducing the behaviour today, they might have fixed it!</div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com2tag:blogger.com,1999:blog-8593187011018911209.post-21405525036740190422018-07-16T20:55:00.000+01:002018-07-16T20:56:10.673+01:00British MEPs go NUTS<div dir="ltr" style="text-align: left;" trbidi="on">
I know what you are thinking, the European Parliament has more than its fair share of nutcases but this is about <a href="https://en.wikipedia.org/wiki/NUTS_statistical_regions_of_the_United_Kingdom">NUTS as in <i style="background-color: white; color: #222222; font-family: sans-serif; font-size: 14px;">Nomenclature des unités territoriales statistiques </i></a> which are defined across Europe, but we'll take the UK as a case study.<br />
<br />
A year and a half ago I looked into grouping counties and unitary authorities together in Tableau to form the<a href="http://tableautziaimesa.blogspot.com/2017/02/mapping-uk-regions.html"> European election constituencies in England</a>. This takes a fair amount of work, and I had a dodgy solution with dual axis to avoid having to also group all Scottish, Welsh and Northern Irish counties in a similar way.<br />
<br />
A much simpler solution using the latest Tableau functionality is to use NUTS as geographic role. This avoids needing a source to define the constituencies at all, so the data source of the MEPs of each region is enough to also generate maps.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiv3F9IvGv-YDx9NQBIWn6WtHparAkJsP32ghPdcJgBsVky9dmvHaWQoHNoF6pOrZl8IX3epdTjyGckaUCPm4GiM-djNhm-Yv6IS5Jgh4WhTdC9kwpT8YKxwpPEAycnqhfv-ZthYScl8Oo/s1600/NUTS.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="1028" data-original-width="952" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiv3F9IvGv-YDx9NQBIWn6WtHparAkJsP32ghPdcJgBsVky9dmvHaWQoHNoF6pOrZl8IX3epdTjyGckaUCPm4GiM-djNhm-Yv6IS5Jgh4WhTdC9kwpT8YKxwpPEAycnqhfv-ZthYScl8Oo/s640/NUTS.png" width="592" /></a></div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-74738823626494125842018-07-07T16:17:00.000+01:002018-07-17T21:46:51.195+01:00Splitting the prefix out of a british postcode with no spaces<div dir="ltr" style="text-align: left;" trbidi="on">
I came across a dataset this week that had postcodes in this format<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_50QFJ2Myc0FN_d_kMMlDjWdzQSldZ2cWx8Ft2PumfEaXl5zaVKP0IAC9s5GXz1ASAxixwIVYxDT-gRyaOV8usYD6Syp8PcbfWMN9HcQzguJqihhgKdufiFwtaXCcDW8-sp5ibtIwC3c/s1600/datasource.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="142" data-original-width="285" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_50QFJ2Myc0FN_d_kMMlDjWdzQSldZ2cWx8Ft2PumfEaXl5zaVKP0IAC9s5GXz1ASAxixwIVYxDT-gRyaOV8usYD6Syp8PcbfWMN9HcQzguJqihhgKdufiFwtaXCcDW8-sp5ibtIwC3c/s1600/datasource.png" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
Tableau only understands the first half of the postcode, but how do we split it out? Wikipedia as always has a <a href="https://en.wikipedia.org/wiki/Postcodes_in_the_United_Kingdom#Outward_code">fairly comprehensive description</a>: The outward code (i.e first half) can be from 2 to four characters, but the inward code (second half) is always three characters. Therefore we can isolate the outward code with a calculation:</div>
<blockquote class="tr_bq" style="clear: both; text-align: center;">
<span style="color: #3d85c6;">left</span>(<span style="color: orange;">[Post Code]</span>,<span style="color: #3d85c6;">len</span>(<span style="color: orange;">[Post Code]</span>)-3)</blockquote>
<div class="separator" style="clear: both; text-align: center;">
This now gives the outward code alone in a field that can be given a post code geographic role and used with the filled map mark type.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4q31FEZIHRwWaL50lWUz5knN_ui5v_abMqQ0cGSrHva467xM1EHeBG0MgU3rEqU9Kjk5HJIRbZvq_O7tvuAPkDLzLWUaP1FTlLCIqImLEtXhD4SFm1anFrDCX3bTK4FB41KqNLgp0brA/s1600/postcode+prefix.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="866" data-original-width="1401" height="393" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4q31FEZIHRwWaL50lWUz5knN_ui5v_abMqQ0cGSrHva467xM1EHeBG0MgU3rEqU9Kjk5HJIRbZvq_O7tvuAPkDLzLWUaP1FTlLCIqImLEtXhD4SFm1anFrDCX3bTK4FB41KqNLgp0brA/s640/postcode+prefix.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
And if you do spatial analysis, that little anomaly on the river Thames would have caught your attention. Lets add place names and streets in the map layers and zoom in:</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6og2kdIBVsAUV9Fj-dmzRZNTin5bzjVjIRiashjY4yjU1fCmJZ5g39BMJqJHCRT1-pmHPrsquMgCxR3-7OIkW0M_kh0NBIqpuPhcQDU2YJE9d8hlIMRK8ECthN_3BpW1N4gc7HZVnC6A/s1600/blackfriars.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="768" data-original-width="1144" height="427" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi6og2kdIBVsAUV9Fj-dmzRZNTin5bzjVjIRiashjY4yjU1fCmJZ5g39BMJqJHCRT1-pmHPrsquMgCxR3-7OIkW0M_kh0NBIqpuPhcQDU2YJE9d8hlIMRK8ECthN_3BpW1N4gc7HZVnC6A/s640/blackfriars.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-2345545991058499782018-06-03T11:19:00.000+01:002018-06-03T11:19:28.475+01:00Simultaneous gantt objects (2018.1 update of using pivot with contracts)<div dir="ltr" style="text-align: left;" trbidi="on">
My <a href="http://tableautziaimesa.blogspot.com/2017/01/using-pivot-with-contracts.html">second post</a> more than a year ago looked at how we find simultaneous gantt type objects. While the method for data preparation still stands, the final plot can be refined a bit thanks to the latest tableau update, the step line type<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyUMcL-8LH3k_Zl-94cESxDEUDOqnxoUgJ6LvYFwt-uWUKfv-sqKfgCcJ4nA0CAobhohUKxMfZOXaWSnCsnuWhi7tYJTvBmzh4nb1fa8SvBCRuV02cm_FSeecnwsZRGcQ7oKXURbgxKWo/s1600/new+sheet.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="782" data-original-width="721" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiyUMcL-8LH3k_Zl-94cESxDEUDOqnxoUgJ6LvYFwt-uWUKfv-sqKfgCcJ4nA0CAobhohUKxMfZOXaWSnCsnuWhi7tYJTvBmzh4nb1fa8SvBCRuV02cm_FSeecnwsZRGcQ7oKXURbgxKWo/s640/new+sheet.png" width="588" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
This now gives a more realistic depiction of the number of concurrent objects and how they change, as it is going to be a step change through integer values rather than a ramp up.</div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGvlQvA6bPIpBQ2cOlOc44rlOGbd19ascuM-rzm8W_VPtQ-OBoeuMakdFeHxA7o2LgCklXVfWp8S-GF5UVNQFNdppJ2fa1fe5V3cl7RS8PY7m0iwYrH0elzPpXM9LUZdk6trgQX9eE6eY/s1600/comparison+dashboard.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1041" data-original-width="884" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGvlQvA6bPIpBQ2cOlOc44rlOGbd19ascuM-rzm8W_VPtQ-OBoeuMakdFeHxA7o2LgCklXVfWp8S-GF5UVNQFNdppJ2fa1fe5V3cl7RS8PY7m0iwYrH0elzPpXM9LUZdk6trgQX9eE6eY/s640/comparison+dashboard.png" width="542" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
<br /></div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-10805579817110872972018-06-01T21:44:00.000+01:002018-07-17T21:47:13.777+01:00Layering filled maps and polygons on map (2018.1 update)<div dir="ltr" style="text-align: left;" trbidi="on">
I <a href="http://tableautziaimesa.blogspot.com/2017/02/layering-marks-and-polygons-on-map.html">wrote about this technique</a> more than a year ago and it was one of my more popular posts, thanks to a tweet by zen master <a href="https://twitter.com/ChrisLuv">Chris Love</a>. It is time to update the particular example as version 2018.1 finally brings generated Latitude and Longitude closer to Latitude and Longitude already in the dataset.<br />
<br />
We start by UNIONing our two sources, the polygon source having latitude and longitude, the mark source having a field that can be interpreted as a filled map.<br />
<div class="separator" style="clear: both; text-align: center;">
</div>
<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh50EFemywiIQx-UVwyBQXM6hUtTXsI6Js3L0dFi8AGnSIN-I-qOrBPm_S_Tek10eXbOKoCdx8qdTFSOAo7r8_-0jK4ZAtGFWCRXx7tJ9nRfR6EgWUeIOURQklAdDyWtSiGBn9QsMXAAsM/s1600/datasource+union.png" imageanchor="1" style="clear: left; float: left; margin-bottom: 1em; margin-right: 1em;"><img border="0" data-original-height="666" data-original-width="943" height="282" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh50EFemywiIQx-UVwyBQXM6hUtTXsI6Js3L0dFi8AGnSIN-I-qOrBPm_S_Tek10eXbOKoCdx8qdTFSOAo7r8_-0jK4ZAtGFWCRXx7tJ9nRfR6EgWUeIOURQklAdDyWtSiGBn9QsMXAAsM/s400/datasource+union.png" width="400" /></a></div>
We then use the new trick of putting generated latitude and longitude on the axis, with source latitude longitude in the level of detail for the polygon layer. In the past I would import the filled map source, generate the latitudes longitudes, export crosstab to excel, save as csv, and do a union with the polygon csv. No longer necessary as of 2018.1.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNUo8kTLBbme8tojFoQcCfL_NiROipKhLOH3nnCpXeqf9ll6xEdFIX7fqbHsSwH0GNmcqNIv8AsGWECUAr5qXoqoFfAoNexIOC_y1JWcACc5HGbLB6X3eO3KQEgWmhGohC8ikTaMD5Rj4/s1600/overlay+step+1.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="843" data-original-width="1011" height="531" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhNUo8kTLBbme8tojFoQcCfL_NiROipKhLOH3nnCpXeqf9ll6xEdFIX7fqbHsSwH0GNmcqNIv8AsGWECUAr5qXoqoFfAoNexIOC_y1JWcACc5HGbLB6X3eO3KQEgWmhGohC8ikTaMD5Rj4/s640/overlay+step+1.png" width="640" /></a></div>
Now all that is required is dual axis to overlay the polygon over the filled map.<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6nW6sU_i6RQ1aFWO1uLaRTgB4LGDPJ098X7UWt3-SJWRr0ZAx8-Ur9B34SgB10DezVLsc8la3TCBHQqsySJATWwBlRBE48ehQ7AmCzf27IUF2ZfD8A5_R7pKs2jMV0eY6E2NzUdbADJA/s1600/overlay+step2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="840" data-original-width="1011" height="530" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj6nW6sU_i6RQ1aFWO1uLaRTgB4LGDPJ098X7UWt3-SJWRr0ZAx8-Ur9B34SgB10DezVLsc8la3TCBHQqsySJATWwBlRBE48ehQ7AmCzf27IUF2ZfD8A5_R7pKs2jMV0eY6E2NzUdbADJA/s640/overlay+step2.png" width="640" /></a></div>
<br /></div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-91966687351954394572018-05-29T21:16:00.000+01:002018-05-30T10:00:29.800+01:00Labelling polygons that go across the prime meridian and its antimeridian<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="MsoNormal">
Consider a dataset describing polygons like the one below:</div>
<br />
<table border="0" cellpadding="0" cellspacing="0" style="border-collapse: collapse; width: 256px;">
<colgroup><col span="4" style="width: 48pt;" width="64"></col>
</colgroup><tbody>
<tr height="20" style="height: 15.0pt;">
<td class="xl65" height="20" style="height: 15pt; text-align: right; width: 48pt;" width="64">lat</td>
<td class="xl65" style="border-left: none; text-align: right; width: 48pt;" width="64">lon</td>
<td class="xl65" style="border-left: none; text-align: right; width: 48pt;" width="64">path</td>
<td class="xl65" style="border-left: none; text-align: right; width: 48pt;" width="64">polygons</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">-1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">2</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">-1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">-1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">3</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">-1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">4</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">5</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">179</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">2</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">-1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">179</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">2</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">2</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">-1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">-179</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">3</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">2</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">-179</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">4</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">2</td>
</tr>
<tr height="20" style="height: 15.0pt;">
<td align="right" class="xl65" height="20" style="border-top: none; height: 15.0pt;">1</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">179</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">5</td>
<td align="right" class="xl65" style="border-left: none; border-top: none;">2</td>
</tr>
</tbody></table>
<br />
<div class="MsoNormal">
Tableau doesn't allow you to label polygons directly, therefore the work around is to do dual axis on either latitude or longitude, and create a second layer using the average latitude and longitude of all the vertices of the polygon (key detail, remove the path from the level of detail, while you need it there for the polygon layer) to place the label. </div>
<div class="separator" style="clear: both; text-align: left;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBdFKnLGlg_Bm7vdzHcWyI4oEi5hRdeKgBs3YvXWe8Xd1q6YEicHzlajHQL3sHvlqRNfax-2uBp3S6-QgWkWNwaiAmgoiXhBC4dvDJmcDPTFd_CncbfIS0UDPyMmV-HjxqJl_Lq_T5L80/s1600/avg_lon.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="628" data-original-width="1600" height="249" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhBdFKnLGlg_Bm7vdzHcWyI4oEi5hRdeKgBs3YvXWe8Xd1q6YEicHzlajHQL3sHvlqRNfax-2uBp3S6-QgWkWNwaiAmgoiXhBC4dvDJmcDPTFd_CncbfIS0UDPyMmV-HjxqJl_Lq_T5L80/s640/avg_lon.png" width="640" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
This works fine apart from any polygons that include points on either side of the antimeridian at 180 degrees longitude, using the convention of 0 to 180 for longitude east, and 0 to -180 for longitude west. The trick here is to detect if the maximum and minimum longitude have different signs and if difference between the maximum and the minimum longitude defines an angle smaller or greater than 180 degrees. In the latter case, the longitude convention needs to be changed to 0 to 360 for the averaging to give sensible results. To achieve that we create a calculated field as shown below and use it in place of the longitude.</div>
<div class="MsoNormal">
<br /></div>
<div class="MsoNormal">
if <span style="color: #3d85c6;">max</span>(<span style="color: orange;">[Lon]</span>)*<span style="color: #3d85c6;">min</span>(<span style="color: orange;">[Lon]</span>)<0 then<o:p></o:p></div>
<div class="MsoNormal">
if <span style="color: #3d85c6;">max</span>(<span style="color: orange;">[Lon]</span>)-<span style="color: #3d85c6;">min</span>(<span style="color: orange;">[Lon]</span>)>180 then <span style="color: #3d85c6;">avg</span>(if <span style="color: orange;">[Lon]</span><0 then
360+<span style="color: orange;">[Lon]</span> else <span style="color: orange;">[Lon]</span> end) <o:p></o:p></div>
<div class="MsoNormal">
else <span style="color: #3d85c6;">avg</span>(<span style="color: orange;">[Lon]</span>) end<o:p></o:p></div>
<div class="MsoNormal">
else <span style="color: #3d85c6;">avg</span>(<span style="color: orange;">[Lon]</span>) <o:p></o:p></div>
<div class="MsoNormal">
end<o:p></o:p></div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-18793741135660571872018-05-27T11:33:00.000+01:002018-05-27T11:33:05.727+01:00Spatial filtering by distance in km from a known point<div dir="ltr" style="text-align: left;" trbidi="on">
<br />
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;"> Tableau has supported the circular select tool for a few versions now. So the lazy way out is to use this select option, click at the known point on the map and then drag watching the radius of the circle until it reaches the desired value (tip: change the workbook locale to English Ireland for it to be in kilometres rather than miles). This only works for small distances though, what if you want to be a zoom level further out and look at hundreds and thousands of kilometres, or even use this distance to do something else such as filtering?</span><br />
<span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;"><br /></span>
<span style="color: black; font-family: calibri, sans-serif; font-size: 11pt;"><span style="color: black; mso-style-textfill-fill-alpha: 100.0%; mso-style-textfill-fill-color: black;">Let's port the <a href="https://www.movable-type.co.uk/scripts/latlong.html">haversine formula</a> into Tableau</span></span></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<br /></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">Assuming our fixed point is at 52N 0E (consider using parameters with lists of values if you have several points of interest), we create a calculated field step_1:<o:p></o:p></span></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<br /></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">sin</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">radians</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(52-</span><span style="color: #ed7d31; font-family: "calibri" , sans-serif; font-size: 11.0pt;">[Latitude]</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">)/2) *
</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">sin</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">radians</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(52-</span><span style="color: #ed7d31; font-family: "calibri" , sans-serif; font-size: 11.0pt;">[Latitude]</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">)/2) +<o:p></o:p></span></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">
</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">cos</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">radians</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #ed7d31; font-family: "calibri" , sans-serif; font-size: 11.0pt;">[Latitude]</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">)) * </span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">cos</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">radians</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(52)) *<o:p></o:p></span></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">
</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">sin</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">radians</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(0-</span><span style="color: #ed7d31; font-family: "calibri" , sans-serif; font-size: 11.0pt;">[Longitude]</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">)/2) * </span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">sin</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">radians</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(0-</span><span style="color: #ed7d31; font-family: "calibri" , sans-serif; font-size: 11.0pt;">[Longitude]</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">)/2)<o:p></o:p></span></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<br /></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">Then the distance in km from the point defined in step 1 is given by:<o:p></o:p></span></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<br /></div>
<div class="xmsonormal" style="background: white; margin-bottom: .0001pt; margin: 0cm;">
<span style="font-family: "calibri" , sans-serif; font-size: 11pt;">2*</span><span style="color: #ed7d31; font-family: "calibri" , sans-serif; font-size: 11.0pt;">[R]</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;"> *
</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">atan2</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">sqrt</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(</span><span style="color: #ed7d31; font-family: "calibri" , sans-serif; font-size: 11.0pt;">[step_1]</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">), </span><span style="color: #3d85c6; font-family: "calibri" , sans-serif; font-size: 11.0pt;">sqrt</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">(1-</span><span style="color: #ed7d31; font-family: "calibri" , sans-serif; font-size: 11.0pt;">[step_1]</span><span style="color: #212121; font-family: "calibri" , sans-serif; font-size: 11.0pt;">))<o:p></o:p></span></div>
<br />
Where R has been defined as the radius of the earth in km: 6371.<br />
<br />
<br /></div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-84058678447219424602018-05-20T11:18:00.000+01:002018-05-20T11:18:15.308+01:00Using 24 colours in Tableau<div dir="ltr" style="text-align: left;" trbidi="on">
Yet again Tableau tells me it's running out of colours<br />
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmKJenRTP5GXyfdvaURGfYniNTM3ozTiqwrQOw5Cb2ijvm9ceDDP-0r9L2mWBrPqfBUaQf40gg3PfNdno2dCZ7dvoevjARBT8vObwBBUJwkbU2QHhITxVRvXLORsrsYVI38-rveM5_MdU/s1600/more+than+20.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="228" data-original-width="462" height="195" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgmKJenRTP5GXyfdvaURGfYniNTM3ozTiqwrQOw5Cb2ijvm9ceDDP-0r9L2mWBrPqfBUaQf40gg3PfNdno2dCZ7dvoevjARBT8vObwBBUJwkbU2QHhITxVRvXLORsrsYVI38-rveM5_MdU/s400/more+than+20.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
I've found a solution that works, it also adds a retro touch as it brings back some of the classic template colours us Tableau old timers grew to know and love. </div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUSnsprtBYtC9M620GNW37TJnIHzCnV8DvK1jHul_UIeSyoLXJeB8zlzAlipQwmCJdx4pQ7tPtoMCOhbRwRFmxdX3rXhyatWM9305JlDit7zzFFQmK4GyT_VsTevFAVbpxYXwyzZdChqo/s1600/24+colours.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="569" data-original-width="1010" height="225" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUSnsprtBYtC9M620GNW37TJnIHzCnV8DvK1jHul_UIeSyoLXJeB8zlzAlipQwmCJdx4pQ7tPtoMCOhbRwRFmxdX3rXhyatWM9305JlDit7zzFFQmK4GyT_VsTevFAVbpxYXwyzZdChqo/s400/24+colours.png" width="400" /></a></div>
<div class="separator" style="clear: both; text-align: center;">
I click on edit colours, and I select the Classic 20 palette, and then I only change the colours corresponding to 20-23 where it's reusing colours for 0-3, making sure I select the colours in the old palette that no longer exist in the new. </div>
</div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0tag:blogger.com,1999:blog-8593187011018911209.post-62355551245908836782018-05-04T20:35:00.000+01:002018-05-04T20:35:15.428+01:00BBC News website Carto election map epic fail<div dir="ltr" style="text-align: left;" trbidi="on">
<div class="separator" style="clear: both;">
Meanwhile, in the land where elections happen on Thursdays, this is what happens to the public broadcaster website on the day after!</div>
<div class="separator" style="clear: both; text-align: left;">
<br /></div>
<div class="separator" style="clear: both; text-align: center;">
<a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhK3K5mlidy2oIT7tjU0NqviM7mZaQk61oP-eHXqxz-pVK8jtEFXj4DdLi7O6WbfRRCVXKkmDqpyaGw4rnv_meESP3X4huqajPW1tM0Uq7Oh8qRM3rg4sEaxb-rSbR_GZrVghKZ_ztY4X0/s1600/500+error.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="743" data-original-width="1017" height="467" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhK3K5mlidy2oIT7tjU0NqviM7mZaQk61oP-eHXqxz-pVK8jtEFXj4DdLi7O6WbfRRCVXKkmDqpyaGw4rnv_meESP3X4huqajPW1tM0Uq7Oh8qRM3rg4sEaxb-rSbR_GZrVghKZ_ztY4X0/s640/500+error.png" width="640" /></a></div>
<br /></div>
Stelioshttp://www.blogger.com/profile/06459011834395934281noreply@blogger.com0