Excel examples for your work, sports and more.: March 2022

29 Mar 2022

Tableau report of Ukraine-refugees and destination-countries

#83: Tableau report of Ukraine-refugees and destination-countries

I made already a report 'Ukraine-refugees and destination-countries' with Microsoft Power BI (see this post) and Google Data Studio (see this post), and this post is about a report I made with Tableau Online (a free 2 weeks trial version).

Tableau has several (BI-)products, see:

https://www.arkatechture.com/blog/tableau-101-the-difference-between-tableau-products-plus-infographic

I already worked once with the free Tableau Public (see this post about a Covid-19 dashboard), and this time I chose to use Tableau Online, the hosted version of Tableau Server, so no need to install any software.

As a datasource I wanted to reuse the Google-Sheet for my Google Data Studio report (see this post), but unfortunately it is not possible to use a doc from G-Drive:

With Tableau Public, I could use a G-Sheets doc as a datasource for my (Covid-)report.

Anyway, I downloaded the 3 tables (3 worksheets) from G-sheets to CSV-files and used these files for my Tableau-report. So the data in this report is not automatically refreshed (as it is for the Power BI report and Data Studio report), but has the data from the UNHCR-dataportal as of today (29/3/2022).

I wanted to share my report on https://public.tableau.com/s/ , but to do this, I had to use Tableau Public. And I had to embed the datasources (3 CSV-files) of my report, using Tableau-extracts:

https://help.tableau.com/current/online/en-us/datasource_extract.htm

For my post on Tableau Public, see:

https://public.tableau.com/views/UkraineRefugees2022/UkraineRefugeesDashboard?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link

Embedded report

PEACE TO UKRAINE!

Source:

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

References

https://public.tableau.com/views/UkraineRefugeeAnalysis/Sheet2?:language=en-US&:display_count=n&:origin=viz_share_link

https://public.tableau.com/app/profile/andriantseheno.tiana.val.riane/viz/UKRAINEREFUGEESBINTITIANA/Tableaudebord1

https://public.tableau.com/app/profile/info.unit/viz/UkrainesHumanitarianCrisisMapv2/UkraineIDPmap

https://public.tableau.com/app/profile/.45545509/viz/UkraineWarDataVisualizationsDashboard/1_2

Downloads

Tableau-report

27 Mar 2022

Google Data Studio report of Ukraine-refugees and destination-countries

#82: Google Data Studio report of Ukraine-refugees and destination-countries

The war between Ukraine and Russia is already going on for more than a month now, and the number of Ukraine-refugees who fled to a neighbor-country is close to 4M now. Also another 6M people fled to other safer regions in Ukraine. So in total aprox. 10M have left their homes now. Of them aprox. 4M are children, which is about half of the country's children, see e.g.:

https://www.washingtonpost.com/kidspost/2022/03/26/war-ukraine-has-forced-half-nations-children-flee-their-homes/

https://www.bbc.com/news/world-60555472

A previous post was about a Power BI report that I made with as a source UNHCR-data of Ukraine-refugees, see:

https://worktimesheet2014.blogspot.com/2022/03/power-bi-flow-map-of-ukraine-refugees.html.

In this post I made a similar report, but now with Google Data Studio:

https://datastudio.google.com/reporting/53d717be-f008-49b9-bc67-a2daeece66b7

The data-source of this report is a Google-Sheets that I made:

https://docs.google.com/spreadsheets/d/e/2PACX-1vSX2Oxyqwy6qA2RBRkBkmvlEu_npVea1ZPp6bE7AWBxue6rRIUhL2r_-53iQXyLqPoRH7LyvLMlw4S3/pubhtml

Here I import the JSON-files of the UNHCR data-portal. G-Sheets does not have a built-in function do do this, but there is a G-Sheets extension for this which I used:

https://nodatanobusiness.com/resources/importjson-your-first-importjson-function/

https://www.youtube.com/watch?v=EXKhVQU37WM - IMPORTJSON Function - Google Sheets Tutorial - How to Import JSON feed to Spreadsheets

NB: This add-on has a limit of #refreshes per day (100), but it looks there is a workaround:
https://discourse.gbif.org/t/api-importjson-spreadsheet-quota-limit-reached/2218

To format this data in table-format and in a separate worksheet (needed for Google Data Studio), I defined named ranges and used the QUERY-function:

https://infoinspired.com/google-docs/spreadsheet/learn-query-function-with-examples-in-google-sheets/

https://www.youtube.com/watch?v=kQ7DKx3eZQg - Create a Data Table in Google Sheets Like Excel

PEACE TO UKRAINE !

Source:

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

References

https://www.statista.com/study/86697/russia-ukraine-conflict/#professional

https://www.forbes.com/sites/rashishrivastava/2022/03/15/how-one-google-doc-is-helping-thousands-of-ukrainian-refugees-navigate-borders/?sh=6c2fd8fed18e

https://twitter.com/Schwarzenegger/status/1504426844199669762

https://eacnur.org/es

Embedded Report

13 Mar 2022

Power BI dashboard with Flow Map Ukraine-refugees and Map NATO-countries

#81: Power BI dashboard with Flow Map Ukraine-refugees and Map NATO-countries

Today is the 18th day of the war between Ukraine and Russia and there are now 2.6 Ukraine-refugees.

In my previous blog-post, there was a live-tracker of the number of refugees based on this page, and I saw it was not updated, but that is fixed now. I also added some elements to the report, and some more details to that post.

In this post I wanted to combine the Flow Map of this live tracker report with the report'
'NATO-countries Map from this post.
Combining reports (or elements of it) is something you can do with Dashboards

Here is the result:

Note that on 2 tiles (coming from the report with the live-tracker of the number of refugees) you can see when it was refreshed.

You can also add tiles with other things than a link to a report, see e.g. the tile with a link to:

https://en.wikipedia.org/wiki/Russo-Ukrainian_War

See also the sample-comment I made, and by using a tag @.., an email is send to that person.

Update 15/3/2022:

I just saw that the Power BI report on the UNHRC data-portal changed the visualization from bubble-map to flow-map, and a nicer one than that of the custom-visual I used:

PEACE TO UKRAINE !

source pic:

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

References

https://www.unhcr.org/nl/2022/03/dataportal-cijfer-vluchtelingen-oekraine/

https://www.theatlantic.com/photo/2022/03/photos-ukrainian-refugees-say-goodbye-home-and-family-members/626964/

https://news.sky.com/topic/data-and-forensics-9532

https://www.youtube.com/watch?v=yzNxLzFfR5w - Winter on Fire: Ukraine's Fight for Freedom | Full Feature | Netflix

5 Mar 2022

Power BI Flow Map of Ukraine refugees and destination countries

#80: Power BI Flow Map of Ukraine refugees and destination countries

Update 13/5/2022:
I made some improvements in the report, see Embedded report (at bottom), or see my post on the Power BI Data Stories Gallery here.

Today is the 10th day of the war between Ukraine and Russia and there are already over 1M Ukraine-refugees.

I saw here a flow map which shows to which countries they flee:

https://www.bbc.com/news/world-60555472#:~:text=Which%20countries%20are%20Ukraine's%20refugees,gone%20to%20Russia%20and%20Belarus.

https://www.aljazeera.com/news/2022/2/28/russia-ukraine-crisis-in-maps-and-charts-live-news-interactive\

I made something similar in Power BI, using the custom-visual Flow Map, for more info, see:

https://weiweicui.github.io/PowerBI-Flowmap

with as data source a Jason-file from the UNHCR site:

https://data2.unhcr.org/population/get/sublocation?widget_id=283559&sv_id=54&population_group=5459,5460&forcesublocation=0&fromDate=1900-01-01

This JSON-file I found here:

https://data2.unhcr.org/en/situations/ukraine

Below the map you can see a JSON-feed, with URL:

https://data2.unhcr.org/population/get/sublocation?widget_id=284488&sv_id=54&population_group=5459,5460&forcesublocation=0&fromDate=1900-01-01

and to view this file, you can use this nice (free) online JSON-file viewer:

https://jsonformatter.org/json-viewer

And I conclude this blog-post with the same picture as in the last post.
So as you can see in the URL, it is made by a Russian designer, wishing

PEACE TO UKRAINE

https://euromaidanpress.com/2014/03/02/russian-designer-creates-icon-for-peace-in-ukraine-using-the-shapes-of-the-national-symbolic/

Interesting links

https://www.nytimes.com/2022/03/01/world/europe/ukraine-war-migration.html

Download

Power BI report

Embedded report

2 Mar 2022

#79: Power BI Map of Europe and its alliances NATO EU Euro

Because of the war between Ukraine and Russia, and the importance of NATO-membership for especially East European countries that border with Russia as e.g. the Baltic states (Estonia, Latvia and Lithuania), I was wondering if it would be possible to make visualization in Power BI that can show for all European countries their membership in the most important military, political and economic alliances (NATO, EU and Euro), in one map. I came up with this solution, so a map with a pie-chart for each country that can have between 1 and 4 parts (Europe, EU, Euro, NATO):

REPORT

Note that in this pic, I filtered on field Indicator = NATO (see legend), which is the highlighted purple pie in the chart.

And if you zoom-out, you can see which other countries in the world belong to NATO.

And to my surprise I also saw that outside Europe, there are some countries were the Euro is used (ex-colonies of Europe, e.g. French Guiana):

DATA

When I googled to find a list with NATO/EU/Euro-countries, I found this site:

https://www.wolframalpha.com

Here you can formulate a natural language query using the Wolfram(computational) language
which does:

Compute expert-level answers using Wolfram’s breakthrough algorithms, knowledgebase and AI technology

and you get the result like this (I used the plain text version):

https://www.wolframalpha.com/input?i=countries+in+nato

MODEL

For the 4 datasets (Wolfram-output files), I made a dataset in Power BI, pivoted the 1-line-file (a list of countries) to have a table of countries, and added an indicator as e.g. 'NATO' for the NATO-dataset. And the last step was to 'union' these 4 datasets into 1 dataset: