tag:blogger.com,1999:blog-89342799373634312352024-03-27T16:52:36.194-07:00Vizible DifferenceIn search of data visualization excellenceAlexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.comBlogger310125tag:blogger.com,1999:blog-8934279937363431235.post-14376133681088069242024-01-23T22:27:00.000-08:002024-01-24T13:40:51.346-08:00A Preview of Sankey Chart Type in Tableau 2024<p><span style="background-color: #fcff01;">[Update: The product manager Wilson Po alerted me that the Viz Extension is still a work in progress. It will not be part of the incoming version 2024.1. Instead, it will be released later in 2024. Just be patient]</span></p><p>Tableau 2024.1 is coming. I got a chance to test drive it. As I wrote <a href="https://vizdiff.blogspot.com/search/label/Sankey%20Chart" target="_blank">a bunch of posts on Sankey chart tutorials</a> in the past, I am most excited by the new Sankey chart type. Here I would like to share what I learnt. This is a quick preview. Your comments are welcome.</p><p>I created both dark mode and light mode of a 4-level Sankey chart. The design is a breeze as I did it <a href="https://vizdiff.blogspot.com/2023/05/multi-level-sankey-with-new-sankey.html" target="_blank">before</a>.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQ7RFwuzA23fPnF25XOXDeo9RJxjMaopbp4aTIQWDUgAYmYjtkEgjJAUAW8udzGeH6Gxf1Xa7p69fwMYPOCHvuCkPATqQKs2Kn1Y3sCailBaFyc3Sblfzdo5euhDt7upf1uAL8zpBSiEJ_EXIaGCLE9lbZZW5bAgZHKPuE5vbMzlgkQ01hi1insBmjg5Ny/s2586/Screenshot%202024-01-23%20at%209.51.34%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1584" data-original-width="2586" height="392" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQ7RFwuzA23fPnF25XOXDeo9RJxjMaopbp4aTIQWDUgAYmYjtkEgjJAUAW8udzGeH6Gxf1Xa7p69fwMYPOCHvuCkPATqQKs2Kn1Y3sCailBaFyc3Sblfzdo5euhDt7upf1uAL8zpBSiEJ_EXIaGCLE9lbZZW5bAgZHKPuE5vbMzlgkQ01hi1insBmjg5Ny/w640-h392/Screenshot%202024-01-23%20at%209.51.34%20PM.png" width="640" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHSknvBTARx3HoNZ4cO_K36QrL0-HSAAa5hvr5UpGiaEvU48veo7lamqiXhc7gjuCR3nzWmx9ySFcPmhL2YUoqFfuVi3XLcftTHee29ZT-Mo73EBjdUCWGAu61hAaXj8rLm1UZyL87Edw0HgiGdT36h5hGaHIBRM8KadZAxEDfzW-B9gmOVKi2M_1dW3yg/s1984/Screenshot%202024-01-23%20at%209.52.04%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1590" data-original-width="1984" height="512" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjHSknvBTARx3HoNZ4cO_K36QrL0-HSAAa5hvr5UpGiaEvU48veo7lamqiXhc7gjuCR3nzWmx9ySFcPmhL2YUoqFfuVi3XLcftTHee29ZT-Mo73EBjdUCWGAu61hAaXj8rLm1UZyL87Edw0HgiGdT36h5hGaHIBRM8KadZAxEDfzW-B9gmOVKi2M_1dW3yg/w640-h512/Screenshot%202024-01-23%20at%209.52.04%20PM.png" width="640" /></a></div><p>1.The new chart type needs to be imported as an add-on, aka Viz Extension. This opens the door to many new bespoke chart types by 3rd parties. WoW!</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV8U4-iZgtPnbySuzr3zUd2h8Ub1cuJMI0uKzgZ5ApchwOo5j7tY1JTshNnfJVus4Ev3soCDBBlpfMy0mf6NjWVv8Mpnt9749eI8UHgmrv6N_6fo4z9HIeodgrAn5yuuM0xF9-vxi-dqxtKbjUlo8VB0Kle871vFtMvuYaSI3FMTJqp6Ciq1JAbJ14tbr-/s944/Screenshot%202024-01-23%20at%2010.01.36%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="944" data-original-width="352" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgV8U4-iZgtPnbySuzr3zUd2h8Ub1cuJMI0uKzgZ5ApchwOo5j7tY1JTshNnfJVus4Ev3soCDBBlpfMy0mf6NjWVv8Mpnt9749eI8UHgmrv6N_6fo4z9HIeodgrAn5yuuM0xF9-vxi-dqxtKbjUlo8VB0Kle871vFtMvuYaSI3FMTJqp6Ciq1JAbJ14tbr-/w238-h640/Screenshot%202024-01-23%20at%2010.01.36%20PM.png" width="238" /></a><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEii0bbNalb9AqsXIab8iRsIl0YRzU3xzRZxQX9gJ0b3-FnpBqs2KQIzfMroV8eGZKataeiM4za6LI2evX_YG6f5ownlVLf6CcKd8jojusN_wfzZ-CII1gHcBmcjB2rbb_N3peHYSOdQfGzhs4CYpjH-SV0D0jvTFpb8Ko8FugwMMf3s4GouAwhqE4IpuYQT/s624/Screenshot%202024-01-23%20at%2010.01.14%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="624" data-original-width="368" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEii0bbNalb9AqsXIab8iRsIl0YRzU3xzRZxQX9gJ0b3-FnpBqs2KQIzfMroV8eGZKataeiM4za6LI2evX_YG6f5ownlVLf6CcKd8jojusN_wfzZ-CII1gHcBmcjB2rbb_N3peHYSOdQfGzhs4CYpjH-SV0D0jvTFpb8Ko8FugwMMf3s4GouAwhqE4IpuYQT/w378-h640/Screenshot%202024-01-23%20at%2010.01.14%20PM.png" width="378" /></a></div><p>This is actually following the same philosophy behind Tableau's product design for many years. Any dashboard can be a chart type because we can get a copy of the workbook and plug in our own data by replacing data sources. Voila, in theory, we can create our own version of the same dashboard in no time.</p><p>Now new chart types can be created and shared at a much lower level or lower granularity of a dashboard. I can foresee that it will spawn a new wave of creativities and provide a wide array of design options.</p><p>2.The new chart type is only available on the web editor. So we won't see it in Tableau Desktop.</p><p>For the moment, the web editor still lag behind the desktop in terms of functionalities. For example, the formatting on the web editor has much less options.</p><p>3.I can't format the headers of column bars in the Sankey chart</p><p>4.There is no way to add tooltips to the column bars. </p><p>5.There is no choice of custom color palettes. The web editor will assign its own color palette to the Sankey chart. We need the ability to color the bars and curves according to our own taste.</p><p>6.I downloaded the workbook from Tableau Cloud server. But I could not display the chart in Tableau desktop 2024.1. It seems I need the Sankey Viz extension in the desktop. I wonder if we will ever have one.</p><p>7.I also tried to download the images of the dashboard. But it doesn't work. The downloaded images are blank. The images in the above are screenshots.</p><p>8.A great feature is that each path can be highlighted easily.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3g495AIoQc_hMJnFjeSf0n6P3rOZXUBn0lAX6infRTytlvmzsmFZ6N7t_k-331ab13h4jE0BDlZYAI-UBFV8DKvXgAHAYrC9ccZckS2m_U2kPIS9C4x6OjTG58iWXiaeK4iD0aFLl8bv3nexkcnlt7uZLI_LQZcV2HkQVMaPAhPiEuNGRKENRwhMYLEk4/s1972/Screenshot%202024-01-23%20at%2010.09.36%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="692" data-original-width="1972" height="224" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh3g495AIoQc_hMJnFjeSf0n6P3rOZXUBn0lAX6infRTytlvmzsmFZ6N7t_k-331ab13h4jE0BDlZYAI-UBFV8DKvXgAHAYrC9ccZckS2m_U2kPIS9C4x6OjTG58iWXiaeK4iD0aFLl8bv3nexkcnlt7uZLI_LQZcV2HkQVMaPAhPiEuNGRKENRwhMYLEk4/w640-h224/Screenshot%202024-01-23%20at%2010.09.36%20PM.png" width="640" /></a></div><p>9.The hierarchy of Category and Sub-Category is kept even after I deleted it. I tried to sort Category and Sub-Category independently by deleting the hierarchy. Still sorting the Sub-Category is always nested in Category.</p><p>All the above will be fixed or improved sooner or later. It will definitely popularize the utility of a beloved chart type. Great job, Tableau team!</p>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-22681327370943085612023-06-26T00:40:00.000-07:002023-06-26T00:40:45.750-07:00AI's Perception of People Looks in USA<p>Buzzfeed recently asked Midjourney to draw <a href="https://www.buzzfeed.com/sarathompson1/average-state-person-looks-ai-image" target="_blank">images of people in 50 US states</a>. So the AI drawing tool created 50 images of couples that represent its perception of the people in each state.</p><p>I just put the images into <a href="https://public.tableau.com/views/AIPersonLookUSA/Dashboard1?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">a tiled map in Tableau</a>. Each image is added as a background in each tile.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdzhw3gKCDLhKweH8dw7ZvWgxXga4XHoIW5-eyOmkplNY1flsOohxOTQbnZ7FwALdjeVeoUTfPVuTx4n_hQrgOClDEHF5Ee-wX_mGODTbpJlJ_f1I21bZTBw2yZm-4JMJCN5oIFNlmHIrbxpmZwIFMirkBIAJhVVkImvE2CKjzsgLK3BH690w6jJDcdgql/s1590/Person%20Look%20USA.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1150" data-original-width="1590" height="462" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjdzhw3gKCDLhKweH8dw7ZvWgxXga4XHoIW5-eyOmkplNY1flsOohxOTQbnZ7FwALdjeVeoUTfPVuTx4n_hQrgOClDEHF5Ee-wX_mGODTbpJlJ_f1I21bZTBw2yZm-4JMJCN5oIFNlmHIrbxpmZwIFMirkBIAJhVVkImvE2CKjzsgLK3BH690w6jJDcdgql/w640-h462/Person%20Look%20USA.png" width="640" /></a></div><div class="separator" style="clear: both; text-align: left;">And also I added Viz-in-tooltips to enlarge an image to look at more details.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9ek4KN3Wok7Xx7bNUIEJG-O6C_qDvM9HC_HMgC_3uHqWpyeB9WC-suUKKkkPgAgUlh3-LRybiOKn8Avb3oa_uKxQkpXQEcvUufAVz-IGCWhtHXj38OqlyPdu8RbQnHxj3W3tg2hSoep49mJGXk3D6Ut6Rhy8GPa-hVUhJv89q5-XIusmrO-GejFquyVf2/s1567/Screenshot%202023-06-26%20at%2012.11.30%20AM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1138" data-original-width="1567" height="464" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj9ek4KN3Wok7Xx7bNUIEJG-O6C_qDvM9HC_HMgC_3uHqWpyeB9WC-suUKKkkPgAgUlh3-LRybiOKn8Avb3oa_uKxQkpXQEcvUufAVz-IGCWhtHXj38OqlyPdu8RbQnHxj3W3tg2hSoep49mJGXk3D6Ut6Rhy8GPa-hVUhJv89q5-XIusmrO-GejFquyVf2/w640-h464/Screenshot%202023-06-26%20at%2012.11.30%20AM.png" width="640" /></a></div>Feel free to download <a href="https://public.tableau.com/views/AIPersonLookUSA/Dashboard1?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">the workbook</a> and explore it.<br /><div class="separator" style="clear: both; text-align: left;"><br /></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com1tag:blogger.com,1999:blog-8934279937363431235.post-14711117952382822522023-06-20T04:36:00.002-07:002023-06-20T04:39:38.392-07:00AI's Perception of Top US Banks as a Dog<p><span color="rgba(0, 0, 0, 0.9)" face="-apple-system, system-ui, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", "Fira Sans", Ubuntu, Oxygen, "Oxygen Sans", Cantarell, "Droid Sans", "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Lucida Grande", Helvetica, Arial, sans-serif" style="caret-color: rgba(0, 0, 0, 0.9); font-size: 20px; white-space: pre-wrap;">The folks at <a href="https://www.businessexpert.co.uk/" target="_blank">Business Expert</a></span><span color="rgba(0, 0, 0, 0.9)" face="-apple-system, system-ui, BlinkMacSystemFont, "Segoe UI", Roboto, "Helvetica Neue", "Fira Sans", Ubuntu, Oxygen, "Oxygen Sans", Cantarell, "Droid Sans", "Apple Color Emoji", "Segoe UI Emoji", "Segoe UI Emoji", "Segoe UI Symbol", "Lucida Grande", Helvetica, Arial, sans-serif" style="caret-color: rgba(0, 0, 0, 0.9); font-size: 20px; white-space: pre-wrap;"> had a<span style="font-family: inherit;"> brillian</span>t idea. They asked <a href="https://lnkd.in/gTFj8R9U " target="_blank">AI's perception on UK banks as a dog</a>. I am inspired to do the same on US banks.</span></p><p style="border: var(--artdeco-reset-base-border-zero); box-sizing: inherit; caret-color: rgba(0, 0, 0, 0.9); color: rgba(0, 0, 0, 0.9); counter-reset: list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; cursor: text; font-size: 20px; line-height: var(--artdeco-reset-typography_getLineHeight); margin: 0px; padding: 0px; vertical-align: var(--artdeco-reset-base-vertical-align-baseline); white-space: pre-wrap;"><span style="font-family: inherit;">ChatGPT is asked to confess its perceptions on top US banks as a dog. Then Midjourney is tasked to generate the images. Check out what dog is matched to your favorite bank.</span></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4Wynlykjw-xSd9MYmVTWeGZjk5ucTKKgk1neY5p4pJWnwPE8GGKjZV28_IodZ0Zusaa72io7KrLPfGr4g9OGI7KkBM65ROjqw04sB5jNSmYSQP1oIMOy_uZ5Fl4ehPqA_NB7XGfFeEM7B6zar5tzGLmqHWQ7F-WwENF7pjs46YLPo3RMSAtnegqHxN_9r/s747/Screenshot%202023-06-20%20at%204.28.19%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="747" data-original-width="691" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4Wynlykjw-xSd9MYmVTWeGZjk5ucTKKgk1neY5p4pJWnwPE8GGKjZV28_IodZ0Zusaa72io7KrLPfGr4g9OGI7KkBM65ROjqw04sB5jNSmYSQP1oIMOy_uZ5Fl4ehPqA_NB7XGfFeEM7B6zar5tzGLmqHWQ7F-WwENF7pjs46YLPo3RMSAtnegqHxN_9r/w592-h640/Screenshot%202023-06-20%20at%204.28.19%20AM.png" width="592" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizA_dTYasXEDuOFgqzcxaTNV7N1N5B3uT6QoN7l5qJaHsJctCV2DGxoY6hxp0kI_lDfqrhyBmsak90j4mLULD6wCuzh6BcMntOEXguN8nHQVdKljFcOXLUw02vJq1tQM2eCRjth9jSGTJy-bpdR9zwRI0LpYy39d46o3D7WQY4LZ5T3N69Xgp8-hq2rYot/s746/Screenshot%202023-06-20%20at%204.28.35%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="746" data-original-width="692" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEizA_dTYasXEDuOFgqzcxaTNV7N1N5B3uT6QoN7l5qJaHsJctCV2DGxoY6hxp0kI_lDfqrhyBmsak90j4mLULD6wCuzh6BcMntOEXguN8nHQVdKljFcOXLUw02vJq1tQM2eCRjth9jSGTJy-bpdR9zwRI0LpYy39d46o3D7WQY4LZ5T3N69Xgp8-hq2rYot/w594-h640/Screenshot%202023-06-20%20at%204.28.35%20AM.png" width="594" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5_pNGt5YETXVIEAWJMjjC1SwqlOvo1DuaweJddi8PNiRSDe-7tlcF9VbN_kKkZg3nwlLvzC2j-ytPm1_KNijc7NH4I4McFsh18ATK7byreqRrONRBkwQ1bQRYQhJKgWJBHr3BrGNiKvGiU6rpLeDsUgF6x6SFsmHkZG5E9Zt_nfF578YXtMPyciwwoFPJ/s745/Screenshot%202023-06-20%20at%204.28.50%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="745" data-original-width="691" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg5_pNGt5YETXVIEAWJMjjC1SwqlOvo1DuaweJddi8PNiRSDe-7tlcF9VbN_kKkZg3nwlLvzC2j-ytPm1_KNijc7NH4I4McFsh18ATK7byreqRrONRBkwQ1bQRYQhJKgWJBHr3BrGNiKvGiU6rpLeDsUgF6x6SFsmHkZG5E9Zt_nfF578YXtMPyciwwoFPJ/w594-h640/Screenshot%202023-06-20%20at%204.28.50%20AM.png" width="594" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-P-_JZnWiA144Lsts0mSwjq-xGLTCkWDkKLVTjaV9Cgs_aGDcBqtne5VTQ_kmBIqNCT_y6CtHX20KNMNQ_DgqPjCv9hB9-N1rtZ-Ae8QYS6a0HJiEPhf84YJGSnx6mdaRwZMjUtd0MgedQ4f4LPOoUg1QsYYXQcXHukP1hJd2R6BWkBMdkwcz7BGND82k/s743/Screenshot%202023-06-20%20at%204.29.04%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="743" data-original-width="691" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh-P-_JZnWiA144Lsts0mSwjq-xGLTCkWDkKLVTjaV9Cgs_aGDcBqtne5VTQ_kmBIqNCT_y6CtHX20KNMNQ_DgqPjCv9hB9-N1rtZ-Ae8QYS6a0HJiEPhf84YJGSnx6mdaRwZMjUtd0MgedQ4f4LPOoUg1QsYYXQcXHukP1hJd2R6BWkBMdkwcz7BGND82k/w596-h640/Screenshot%202023-06-20%20at%204.29.04%20AM.png" width="596" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUVo_RyTgYV-E18qPvtwff6T34VSZb_2fWzAn3k8XN-04Le4M1Qw2q5V4JRz-l3S4jOS1xFOCFdNNCeA_pTDWEXqyeFlfI4DwMNg4mCY9Xd0jbC48IZ2LnpPo4C2gixX_M8LFTViBPbpMNBce2G-r_UjrfHSl1t6tcuJZ2xhanpMzTQYcqqwa4aMtPqdiQ/s746/Screenshot%202023-06-20%20at%204.29.15%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="746" data-original-width="689" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgUVo_RyTgYV-E18qPvtwff6T34VSZb_2fWzAn3k8XN-04Le4M1Qw2q5V4JRz-l3S4jOS1xFOCFdNNCeA_pTDWEXqyeFlfI4DwMNg4mCY9Xd0jbC48IZ2LnpPo4C2gixX_M8LFTViBPbpMNBce2G-r_UjrfHSl1t6tcuJZ2xhanpMzTQYcqqwa4aMtPqdiQ/w592-h640/Screenshot%202023-06-20%20at%204.29.15%20AM.png" width="592" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBEFyiX5XjvxNTuAmXSXTYfcDYXp56QL7quEdRgvLIPNphI5LB9wQdCrguebG8RT2hfW2XoBdkZO7rN5U9NhREHrKlmtlf4PE03r7bGOTp4UQhclLCYSbaXkRwVKNK7Siif2D-aoTxo1hY4MoWfrLurYLVdoYuYKwncRng-0Yj1lttaOr845XunCQY8Gwm/s745/Screenshot%202023-06-20%20at%204.29.26%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="745" data-original-width="691" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBEFyiX5XjvxNTuAmXSXTYfcDYXp56QL7quEdRgvLIPNphI5LB9wQdCrguebG8RT2hfW2XoBdkZO7rN5U9NhREHrKlmtlf4PE03r7bGOTp4UQhclLCYSbaXkRwVKNK7Siif2D-aoTxo1hY4MoWfrLurYLVdoYuYKwncRng-0Yj1lttaOr845XunCQY8Gwm/w594-h640/Screenshot%202023-06-20%20at%204.29.26%20AM.png" width="594" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxhQ_oLDRjP9tLieE4Ss1BIGgiE0wsQjLKRdOwWyYdQK6T3WdYYxy4W-LKmJikWld3SyBuPq2uIc2Ec0jL-BXJC3mOiEBAV9jLTQ0eJbyUykvw6E6Mc7yac2VBw61F_r0roszmvtVFrlo9s3WO2h4giw4pwk6E1mwoR3gLLdCT9L881Wd34kbFyjVoPCEw/s745/Screenshot%202023-06-20%20at%204.29.37%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="745" data-original-width="692" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxhQ_oLDRjP9tLieE4Ss1BIGgiE0wsQjLKRdOwWyYdQK6T3WdYYxy4W-LKmJikWld3SyBuPq2uIc2Ec0jL-BXJC3mOiEBAV9jLTQ0eJbyUykvw6E6Mc7yac2VBw61F_r0roszmvtVFrlo9s3WO2h4giw4pwk6E1mwoR3gLLdCT9L881Wd34kbFyjVoPCEw/w594-h640/Screenshot%202023-06-20%20at%204.29.37%20AM.png" width="594" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUuWqSt3KQEx-oCCyrZj6JToboXrroNRvU2o20IezaYHIe4xAh3Dtynm72uxOeh6-21QeAKWXrUSdEphNClA0e4k5XS5oGjf5_Jrt7hZMEqR152SJWUlptzdw2WU5_adoGfHYHsRuNrp2Nqwt_XJaFThRsC_noVCrpR6UQ0nJo094n-EqJdvyfRWxKnvp2/s746/Screenshot%202023-06-20%20at%204.29.52%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="746" data-original-width="691" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhUuWqSt3KQEx-oCCyrZj6JToboXrroNRvU2o20IezaYHIe4xAh3Dtynm72uxOeh6-21QeAKWXrUSdEphNClA0e4k5XS5oGjf5_Jrt7hZMEqR152SJWUlptzdw2WU5_adoGfHYHsRuNrp2Nqwt_XJaFThRsC_noVCrpR6UQ0nJo094n-EqJdvyfRWxKnvp2/w592-h640/Screenshot%202023-06-20%20at%204.29.52%20AM.png" width="592" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWTT4tuC3chrFTvyA6yJz3qWcBSr6xzh2LrtHRJ5IpN3-sh1ohAB0LfqDX6n_wSqwKDgyi8VmWI-sMmbTeXH29E9XtweCBvmx_pGc5dCRnLnM6LvyuF4UUnVjCnYCbUQwS3Z-D_e3bNuOWt2fcnTdDDgGgTNeDn9ArYP6ThPUDbKk2ybOx14cuYM58UBNR/s746/Screenshot%202023-06-20%20at%204.30.05%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="746" data-original-width="692" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiWTT4tuC3chrFTvyA6yJz3qWcBSr6xzh2LrtHRJ5IpN3-sh1ohAB0LfqDX6n_wSqwKDgyi8VmWI-sMmbTeXH29E9XtweCBvmx_pGc5dCRnLnM6LvyuF4UUnVjCnYCbUQwS3Z-D_e3bNuOWt2fcnTdDDgGgTNeDn9ArYP6ThPUDbKk2ybOx14cuYM58UBNR/w594-h640/Screenshot%202023-06-20%20at%204.30.05%20AM.png" width="594" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTVnwTpCQp6eac9egiPuGm4fduleg5mCueNs8xglQWcUDx3Hwxx5XXXd_PJWTJQhpprXDLfnHV5Amd84d4F8rr2uKwrSli6WVjc2WknxTDx96TBQ1oQhsTmgjE_KhfdKh8bj6d8v-X61WVi64S6AZpfhyf_N-MFNoLM9SypPoOLpssD7gVnJSFy_UtAfqb/s746/Screenshot%202023-06-20%20at%204.24.53%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="746" data-original-width="692" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTVnwTpCQp6eac9egiPuGm4fduleg5mCueNs8xglQWcUDx3Hwxx5XXXd_PJWTJQhpprXDLfnHV5Amd84d4F8rr2uKwrSli6WVjc2WknxTDx96TBQ1oQhsTmgjE_KhfdKh8bj6d8v-X61WVi64S6AZpfhyf_N-MFNoLM9SypPoOLpssD7gVnJSFy_UtAfqb/w594-h640/Screenshot%202023-06-20%20at%204.24.53%20AM.png" width="594" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div><p style="border: var(--artdeco-reset-base-border-zero); box-sizing: inherit; caret-color: rgba(0, 0, 0, 0.9); color: rgba(0, 0, 0, 0.9); counter-reset: list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; cursor: text; font-size: 20px; line-height: var(--artdeco-reset-typography_getLineHeight); margin: 0px; padding: 0px; vertical-align: var(--artdeco-reset-base-vertical-align-baseline); white-space: pre-wrap;"><span style="font-family: inherit;">All are put together into a <a href="https://lnkd.in/gYcxVX72" target="_blank">single-sheet <span color="var(--color-text)" style="border: var(--artdeco-reset-link-border-zero); box-sizing: inherit; margin: var(--artdeco-reset-base-margin-zero); padding: var(--artdeco-reset-base-padding-zero); touch-action: manipulation; vertical-align: var(--artdeco-reset-base-vertical-align-baseline);"><b>Tableau</b></span> dashboard</a>. Feel free to check it out.</span></p>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-92084187929922661602023-05-09T00:53:00.002-07:002023-05-20T17:23:33.496-07:00Multi-Level Sankey with the New Sankey Chart Type in Tableau<p>Through <a href="https://vizdiff.blogspot.com/2023/05/a-first-look-into-new-sankey-chart-type.html" target="_blank">my previous post</a> on the new Sankey chart type, I got in touch with Wilson, the product manager leading the development of this new chart type. I made some comments on creating multi-level Sankey via cascading of single Sankey's. He told me it can be done already by dropping more dimensions into the Level card. How could I overlook that functionality when exploring? Maybe it's because there are only two level bars in the <b>Level</b> card icon? Or did I have a fixation on building multi-level Sankey via a Lego approach?</p><p>Oh well, I am really happy that we can build multi-level Sankey charts with a few drag and drops!</p><p>Below are a few varieties of multi-level Sankey chart built with the new chart type.</p><h4 style="text-align: left;">1.Multi-Level Sankey via Chart Type</h4><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><img border="0" data-original-height="1012" data-original-width="1984" height="326" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjmx_Fxh43sP44eP63oc0IRbmxX8uZCkL9tbKdLPDZlfTWlTEbiXLC__gyHY3CbYPvqBfcjFni8CgJ9jOO3Qe_DeQGuK2jH4Z1AHYsr1t7jhn9huLSf-mpW--Z5bsMbZKf7VxHhYZg6LXzpTv70jI-XykqKIZCbCfdyigkkm3Xe6sSbu4A7HXOcfMkUNw/w640-h326/Screenshot%202023-05-08%20at%201.34.58%20PM.png" width="640" /></div><div class="separator" style="clear: both; text-align: justify;"><span style="text-align: left;">We only need 5 drag-n-drops to create this chart: 4 dimensions and 1 measure.</span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqFAuxIb4yC8Ej254gV-YR-rgC8BrgEKCPDlPi3EP7ehy8HuM6zJTUBUJfsxgacAZlpX0cLvFSpkfyyVumpcgsj8woARV0_PVUZ1I1dhGtZFaXYXTdXv65qEyCKPN3r5wEZzJlhi3qbsKho7Zkdf0ZdvvLok6V8mBQZVFYN7KPrTg20_VYTumJvCQPOg/s590/Screenshot%202023-05-08%20at%201.52.01%20PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="590" data-original-width="358" height="320" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiqFAuxIb4yC8Ej254gV-YR-rgC8BrgEKCPDlPi3EP7ehy8HuM6zJTUBUJfsxgacAZlpX0cLvFSpkfyyVumpcgsj8woARV0_PVUZ1I1dhGtZFaXYXTdXv65qEyCKPN3r5wEZzJlhi3qbsKho7Zkdf0ZdvvLok6V8mBQZVFYN7KPrTg20_VYTumJvCQPOg/s320/Screenshot%202023-05-08%20at%201.52.01%20PM.png" width="194" /></a></div><div class="separator" style="clear: both; text-align: center;"><h4 style="text-align: left;">2.Multi-Level Sankey with Level Padding</h4><div class="separator" style="clear: both; text-align: left;">In the <b>Level</b> card, we can set the option to add vertical space between members of a level dimension. This is called <b>Level Padding</b>.</div><div class="separator" style="clear: both; text-align: left;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTa3YpyHy57wXtnFXWjUZdxi3P7trW9yeLQMNre4dhYI6ngAbgYHPWI43J_RsYue-5tosf0e4TvngriVnxeVxP51iGSi3xfkjyqxRL6MrqnQKdnMoAYjpwy9LNapNZHkF0I4jTVH_Uq9fr39HYah3iqW0LJ2mRzjXAR9xDvhAs9DVeZGOqTnJG4RG0kw/s616/Screenshot%202023-05-09%20at%2012.31.01%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="374" data-original-width="616" height="194" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgTa3YpyHy57wXtnFXWjUZdxi3P7trW9yeLQMNre4dhYI6ngAbgYHPWI43J_RsYue-5tosf0e4TvngriVnxeVxP51iGSi3xfkjyqxRL6MrqnQKdnMoAYjpwy9LNapNZHkF0I4jTVH_Uq9fr39HYah3iqW0LJ2mRzjXAR9xDvhAs9DVeZGOqTnJG4RG0kw/s320/Screenshot%202023-05-09%20at%2012.31.01%20AM.png" width="320" /></a></div></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP5zFkevN8fFBgu8DciFCThvc_Z10CxrKtGiThlNrl5ViTx_CvOFHVVPDr2sDkxzN8a0SAvQ1MYMOU3hpRHkIw6WTwuA6l57yOzjiEV-DURa0KjAlvYQyPrIvFMV9jHqRjVb-dJHmrS693N6ix8IhyCyradCzS9hz4y5_Se2mr23vVVAB-RBF2sl8w-Q/s1974/Screenshot%202023-05-08%20at%201.44.06%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="994" data-original-width="1974" height="322" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhP5zFkevN8fFBgu8DciFCThvc_Z10CxrKtGiThlNrl5ViTx_CvOFHVVPDr2sDkxzN8a0SAvQ1MYMOU3hpRHkIw6WTwuA6l57yOzjiEV-DURa0KjAlvYQyPrIvFMV9jHqRjVb-dJHmrS693N6ix8IhyCyradCzS9hz4y5_Se2mr23vVVAB-RBF2sl8w-Q/w640-h322/Screenshot%202023-05-08%20at%201.44.06%20PM.png" width="640" /></a></div><h4 style="text-align: left;">3.Multi-Level Sankey without Level Bars</h4><div class="separator" style="clear: both; text-align: left;">In the <b>Level</b> card, we can set the option to hide/minimize the vertical bars.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyb-vquziIKJfihvL_W6i8PgN0-ZNwbiVI6iOz9gJVzHy-sCslmHhdwyqMkfs_RZ7bl0qRIhCZe1nnKqqbhXzwrwe-wLU-5-BKFgFodATZqvddd0-E_s0fnnaEz-S7EV03Swnu8sGf9jn2emmyPXCpf0WfYyW0_y4LIW7lwCUU-n4kVLkLSOkFMJuLdg/s614/Screenshot%202023-05-09%20at%2012.31.31%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="370" data-original-width="614" height="193" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjyb-vquziIKJfihvL_W6i8PgN0-ZNwbiVI6iOz9gJVzHy-sCslmHhdwyqMkfs_RZ7bl0qRIhCZe1nnKqqbhXzwrwe-wLU-5-BKFgFodATZqvddd0-E_s0fnnaEz-S7EV03Swnu8sGf9jn2emmyPXCpf0WfYyW0_y4LIW7lwCUU-n4kVLkLSOkFMJuLdg/s320/Screenshot%202023-05-09%20at%2012.31.31%20AM.png" width="320" /></a></div></div><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjsZVe3crIuF1WL08AAC12v38C_rcJ6G3TnrBGONcnhSfMyjv12myNpjpsCKxATzSMwTaLZHCHZojLuFAID1F00TmJPWBMgekGJyLCn2kHdFS8lc-TPw-lQcQerbH12ZfrKDo-f75Xfrgz-J-RhYTYWcCShvfHnRxxXBMUIilz1hiOdfGaje8omVWIXw/s1980/Screenshot%202023-05-08%20at%201.44.26%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1000" data-original-width="1980" height="324" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjjsZVe3crIuF1WL08AAC12v38C_rcJ6G3TnrBGONcnhSfMyjv12myNpjpsCKxATzSMwTaLZHCHZojLuFAID1F00TmJPWBMgekGJyLCn2kHdFS8lc-TPw-lQcQerbH12ZfrKDo-f75Xfrgz-J-RhYTYWcCShvfHnRxxXBMUIilz1hiOdfGaje8omVWIXw/w640-h324/Screenshot%202023-05-08%20at%201.44.26%20PM.png" width="640" /></a></div><h4 style="text-align: left;">4.Multi-Level Sankey Showing Labels when Selected or Highlighted</h4><div class="separator" style="clear: both; text-align: left;">We can set the option in the <b>Label</b> card to show labels on links that are selected or higlighted.<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKqPgf9lW0IgUqO5swRLd1mNX1hRMqptTIMYnDof9OIdHUBgfHrtGsWqirKYPPwydmixv4kIDuQ2nOtarJlcIBx69Bl9IJHv3nU2E97jcKyXQ3OTGL_BGps0i1HJ2z_6yylOiNR6U2g1VYqj5MmSd2x1lNN4-AwAuWPMKxKv6kJ8bPI4xrcpTXepliYQ/s526/Screenshot%202023-05-09%20at%2012.32.12%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="482" data-original-width="526" height="293" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKqPgf9lW0IgUqO5swRLd1mNX1hRMqptTIMYnDof9OIdHUBgfHrtGsWqirKYPPwydmixv4kIDuQ2nOtarJlcIBx69Bl9IJHv3nU2E97jcKyXQ3OTGL_BGps0i1HJ2z_6yylOiNR6U2g1VYqj5MmSd2x1lNN4-AwAuWPMKxKv6kJ8bPI4xrcpTXepliYQ/s320/Screenshot%202023-05-09%20at%2012.32.12%20AM.png" width="320" /></a></div></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB5VH1MMgkr4dPHnoRLbYg3fNexNZ5wGvGGn-qyDpbUn_u0GYke3e7UBVb_zDJSsgrrwDEZ0HpPfdjebEFTgoGoGAiuH3pgk98UZDc2uPy4cqTBMTybK_5an_Kc9sVwmyeo0PDHGuiGeRihMadAulIrfWNV8ml24fw0XyoVHa7H_4MvjhYfBbI1EXF0Q/s1984/Screenshot%202023-05-08%20at%201.46.49%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="984" data-original-width="1984" height="318" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhB5VH1MMgkr4dPHnoRLbYg3fNexNZ5wGvGGn-qyDpbUn_u0GYke3e7UBVb_zDJSsgrrwDEZ0HpPfdjebEFTgoGoGAiuH3pgk98UZDc2uPy4cqTBMTybK_5an_Kc9sVwmyeo0PDHGuiGeRihMadAulIrfWNV8ml24fw0XyoVHa7H_4MvjhYfBbI1EXF0Q/w640-h318/Screenshot%202023-05-08%20at%201.46.49%20PM.png" width="640" /></a></div><h4 style="text-align: left;">Comments</h4><div class="separator" style="clear: both; text-align: left;">Overall, the creation of multi-level Sankey chart is a breeze.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">My only observations are as follows.</div><div class="separator" style="clear: both; text-align: left;"><ul style="text-align: left;"><li>Can we have more options in setting the colors of links and bars? </li><li>Can we support Funnel chart through this chart type? People ask me about Funnel chart often.</li><li>In my experience, one decimal percentage is enough.</li></ul><div>The above chart can be viewed <a href="https://public.tableau.com/app/profile/alex.mou2116/viz/SankeyChartTypeEvaluation/Dashboard2#1" target="_blank">here</a>. And it can be explored in web edit. The workbook can't be downloaded because it's a pilot program.</div></div><p></p>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-13926628262128226762023-05-06T18:38:00.005-07:002023-05-20T17:25:30.525-07:00A First Look into the New Sankey Chart Type in Tableau<p>As an enthusiastic user of Sankey charts, I am excited to learn that a Sankey chart type is being piloted in Tableau Public (Web Edit only). I wrote about <a href="https://vizdiff.blogspot.com/search/label/Sankey%20Chart" target="_blank">Sankey chart design</a> in multiple posts. Sankey chart may appear in different forms depending on applications. </p><p>I played a little with it just to evaluate it. Here are my initial findings and comments.</p><h4 style="text-align: left;">1. The basic Sankey</h4><p>I can quickly create a Sankey with 2 dimensions and 1 measure. This is great! </p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBIvqPSi5GtYFikM6-rrkze7kRhrJVc54kVGpI5Lyu33Xu9cdKSHKGHXcp_t0KkJ1dXNcczMor0XcpdEIjNqaX51h6U9uiLjiMLoR7gl_eMbKQ8olH3jvWgTMLoJUzxFTfy6ruiZWsX3hgAJBJ5wdaCcFCfIzKORyJua-zP8Aneorl4HkwI5laXiqSew/s794/Screenshot%202023-05-06%20at%205.23.28%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="507" data-original-width="794" height="409" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgBIvqPSi5GtYFikM6-rrkze7kRhrJVc54kVGpI5Lyu33Xu9cdKSHKGHXcp_t0KkJ1dXNcczMor0XcpdEIjNqaX51h6U9uiLjiMLoR7gl_eMbKQ8olH3jvWgTMLoJUzxFTfy6ruiZWsX3hgAJBJ5wdaCcFCfIzKORyJua-zP8Aneorl4HkwI5laXiqSew/w640-h409/Screenshot%202023-05-06%20at%205.23.28%20PM.png" width="640" /></a></div><p>I noticed that two new marks cards are being used: <b>Level</b> and <b>Link</b>. The two dimensions are placed in <b>Level</b> card and the one measure is placed in <b>Link</b> card.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsHsB8VBk4vI2zflSdgqmxzcQFe4tUuQmGN72NvKsp2GpZHUVajgs72Q-k8dMIjbD2VNtHbaaxDcHlDOe1vZIQtzJtLsN486rwuQvjpdrZSr3bYaYyaom0jV99BfvP7ONHXNqvA-WlnUTYqU-uWWKttiWOaQH19xdOOFwpOKXt2I6F2xKKOmaDivtzKQ/s253/Screenshot%202023-05-05%20at%203.42.30%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="253" data-original-width="182" height="253" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjsHsB8VBk4vI2zflSdgqmxzcQFe4tUuQmGN72NvKsp2GpZHUVajgs72Q-k8dMIjbD2VNtHbaaxDcHlDOe1vZIQtzJtLsN486rwuQvjpdrZSr3bYaYyaom0jV99BfvP7ONHXNqvA-WlnUTYqU-uWWKttiWOaQH19xdOOFwpOKXt2I6F2xKKOmaDivtzKQ/s1600/Screenshot%202023-05-05%20at%203.42.30%20PM.png" width="182" /></a></div><p><b>Comments:</b></p><p>- The text labels are showing by default and <b>not editable</b>. I understand the design choice. More details can be put in the tooltips. Note that the measure is showing in percentage and not the measure itself. That's fine in Sankey. But I hope we can format the percentage. Currently we can't.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj84024fL82_Jlwgw2vIIlW3WLdxvutyMzYdouEVwfJMX1V05Hnjq6DN440Ouh4vORv5WXW-xfoZsHfJY7dEtWGhO-8A394aHyX_gvcuG-GnNQ6os-5rPneFzALkGSkDIGfxlCfWRVA2ilq6Bw4kA4klXMKW8LLJJ9pfPwlqpuk0nHphQyvd1rAz8Uz8w/s311/Screenshot%202023-05-05%20at%203.58.49%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="311" data-original-width="216" height="311" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj84024fL82_Jlwgw2vIIlW3WLdxvutyMzYdouEVwfJMX1V05Hnjq6DN440Ouh4vORv5WXW-xfoZsHfJY7dEtWGhO-8A394aHyX_gvcuG-GnNQ6os-5rPneFzALkGSkDIGfxlCfWRVA2ilq6Bw4kA4klXMKW8LLJJ9pfPwlqpuk0nHphQyvd1rAz8Uz8w/s1600/Screenshot%202023-05-05%20at%203.58.49%20PM.png" width="216" /></a></div><p>- The <b>Label</b> card is no longer the same as in other chart types. The labels have option to show on <b>links</b>/branches that are selected or highlighted or both.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiI729fJDWisFMgsuaPJn1fewTcd_m7bUd7fPM9sQkCCenNEuHl2exeG34LxjkA3wwVnJlwIXKvjAq8i6ngfDEd9ADAblNvAiedlPv7D92GVBOHsDLSgEmP3AMJC1subwV6-RQl-w2CwU5dAJlE67h8fYN3zgjdBq3ZbhqxqWBPFrA0KnfeSX_qNJqeAg/s281/Screenshot%202023-05-05%20at%203.37.08%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="281" data-original-width="267" height="281" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiI729fJDWisFMgsuaPJn1fewTcd_m7bUd7fPM9sQkCCenNEuHl2exeG34LxjkA3wwVnJlwIXKvjAq8i6ngfDEd9ADAblNvAiedlPv7D92GVBOHsDLSgEmP3AMJC1subwV6-RQl-w2CwU5dAJlE67h8fYN3zgjdBq3ZbhqxqWBPFrA0KnfeSX_qNJqeAg/s1600/Screenshot%202023-05-05%20at%203.37.08%20PM.png" width="267" /></a></div><p>- The <b>Color</b> card provides options to a number of color palettes. We as user can't assign color to each member of a dimension. It's pretty rigid. The coloring of the links follows that of the left level/dimension with a lower opacity. I wonder if we should have option to use the colors of either left or right level/dimension for those of the links.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbG-GWgt1OjcD0AIAaWPuypB-5O3GGeke_h9YhhQqkr2rm64M7nEUPfF6vyhGsQuu0ix3gJeQNPervNZWVBc3a8Y9S8Mk3tjRIXUsRie1oir_KPzrDSZm0AyRWaCCXxnB7mwiLn5zDls4JEJf4keukS10Q4ueMAQXSXzsOCvvQ4BKB0b9MevW7pShOgQ/s514/Screenshot%202023-05-05%20at%205.21.43%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="514" data-original-width="236" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjbG-GWgt1OjcD0AIAaWPuypB-5O3GGeke_h9YhhQqkr2rm64M7nEUPfF6vyhGsQuu0ix3gJeQNPervNZWVBc3a8Y9S8Mk3tjRIXUsRie1oir_KPzrDSZm0AyRWaCCXxnB7mwiLn5zDls4JEJf4keukS10Q4ueMAQXSXzsOCvvQ4BKB0b9MevW7pShOgQ/s16000/Screenshot%202023-05-05%20at%205.21.43%20PM.png" /></a></div><h4 style="text-align: left;">2. Sankey with Level Padding</h4><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPhIWSWFIE5JcVYuZwO402DwOJsv_zSJQjWb_O9pOUPAMScw3i6BR52Rpsr6xYF411tVW1V_t1uhmgPZygbJh0LKSNIZXBISThe9sRsVlb64hyuJe1yKwSAtNj8GAIBmw67j2bFanNeOK8YGqRyn7pRSBqUHRlnUiE0rwFiNjUod7ePDp9K-gOqp0OAw/s795/Screenshot%202023-05-06%20at%205.25.20%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="497" data-original-width="795" height="400" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhPhIWSWFIE5JcVYuZwO402DwOJsv_zSJQjWb_O9pOUPAMScw3i6BR52Rpsr6xYF411tVW1V_t1uhmgPZygbJh0LKSNIZXBISThe9sRsVlb64hyuJe1yKwSAtNj8GAIBmw67j2bFanNeOK8YGqRyn7pRSBqUHRlnUiE0rwFiNjUod7ePDp9K-gOqp0OAw/w640-h400/Screenshot%202023-05-06%20at%205.25.20%20PM.png" width="640" /></a></div>We can add vertical spaces between the members in sidebars. This is what we have been doing when creating Sankey charts. The option is in the <b>Level</b> card as shown below. This is being called <b>Level Padding</b>. The padding size can be adjusted appropriately. <div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwq2b2-NzdkD_vGepo3YwDIgQHPdV45LQeAxkeR0MWI1a_UlV5FTWyGwM296cc0fu_8irVQBh0ZSceRdAV7c54tAp8MC12pGgW_kxuUGvgle1lu_nFMZGImgeaovpBAwj2oTlM3YIza4vy_PtltV0Ku8I4YBog5XPRoufnHKAxs7XT8Qg2h1LoMzoYSQ/s306/Screenshot%202023-05-06%20at%205.31.12%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="155" data-original-width="306" height="155" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwq2b2-NzdkD_vGepo3YwDIgQHPdV45LQeAxkeR0MWI1a_UlV5FTWyGwM296cc0fu_8irVQBh0ZSceRdAV7c54tAp8MC12pGgW_kxuUGvgle1lu_nFMZGImgeaovpBAwj2oTlM3YIza4vy_PtltV0Ku8I4YBog5XPRoufnHKAxs7XT8Qg2h1LoMzoYSQ/s1600/Screenshot%202023-05-06%20at%205.31.12%20PM.png" width="306" /></a></div><h4 style="text-align: left;">3. Sankey without Sidebars</h4><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIbXgQJT8sDPfAfsT8JqLFXeJ6wktkZTicmDNmevu15HJffkx2K_7xSoqcQYoHc1W1tJFxO2JM-dQ-jqK5DEwadaIxUeLlzpLbX7gBih5yBXTHb_-EiFhwb0t36R-z0aOQu1rDOKMfyl0rNNf4pBCMVlwJKOzYbk8eNaBI9QPTQAde7yZZKSxl8FOkyA/s787/Screenshot%202023-05-06%20at%205.36.54%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="495" data-original-width="787" height="402" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIbXgQJT8sDPfAfsT8JqLFXeJ6wktkZTicmDNmevu15HJffkx2K_7xSoqcQYoHc1W1tJFxO2JM-dQ-jqK5DEwadaIxUeLlzpLbX7gBih5yBXTHb_-EiFhwb0t36R-z0aOQu1rDOKMfyl0rNNf4pBCMVlwJKOzYbk8eNaBI9QPTQAde7yZZKSxl8FOkyA/w640-h402/Screenshot%202023-05-06%20at%205.36.54%20PM.png" width="640" /></a></div>In the <b>Level</b> card, we can minimize the sidebars by adjusting the Level Width.<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD9VO0pmMsi5vtnbOV6f8YnhApsSXo8yQc2Wbgl-NzNpowvlqg9jhX6jWsCT5oV-1ZynXpYNYbo8AWEvhjFWznBF-g18OuQ3AZnmQNr0gCTRdt0Jj21mS08J77rPIwZYfv_0SVPSfmJmUNpSn0SHvewyZZslKMcFjja4opvFu4UQ0bGJfy56RlGsDw6g/s307/Screenshot%202023-05-06%20at%205.45.08%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="153" data-original-width="307" height="153" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhD9VO0pmMsi5vtnbOV6f8YnhApsSXo8yQc2Wbgl-NzNpowvlqg9jhX6jWsCT5oV-1ZynXpYNYbo8AWEvhjFWznBF-g18OuQ3AZnmQNr0gCTRdt0Jj21mS08J77rPIwZYfv_0SVPSfmJmUNpSn0SHvewyZZslKMcFjja4opvFu4UQ0bGJfy56RlGsDw6g/s1600/Screenshot%202023-05-06%20at%205.45.08%20PM.png" width="307" /></a></div><p style="text-align: left;"><b>Comment:</b></p><div>I would ask that we have the option to minimize/hide either of the sidebars. The need arises in the building of multi-level Sankey chart, where we <a href="https://vizdiff.blogspot.com/2020/01/creating-3-stage-sankey-chart-via.html?q=sankey" target="_blank">cascade single Sankey's</a>. Then one of the two sidebars in a single Sankey would be redundant. We need to be able to hide/minimize it. </div><div><br /></div><div>This way, we will expand the utility of the Sankey chart type to building multi-level Sankey chart.</div><h4 style="text-align: left;">4. Sankey Label Showing when Selected or Highlighted</h4><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisCdLp8aiSyL4DFbvPSuHBcIS_ilx5uHx03CUGCaMnn57FzuUZMwmdmI-5qViJHLkKmhe4dlAjeI9ByHpBz89TY10UtCfRUcTP7sxXUhmdwNme5SrhfBdSbn4kg0SXzhLg1ec7PwQJ5thMK6hgTe7mG-38A_vNZWMO4HB9QmSEmSE0Y1gsXhQ3Dolesg/s781/Screenshot%202023-05-06%20at%205.58.02%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="493" data-original-width="781" height="404" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEisCdLp8aiSyL4DFbvPSuHBcIS_ilx5uHx03CUGCaMnn57FzuUZMwmdmI-5qViJHLkKmhe4dlAjeI9ByHpBz89TY10UtCfRUcTP7sxXUhmdwNme5SrhfBdSbn4kg0SXzhLg1ec7PwQJ5thMK6hgTe7mG-38A_vNZWMO4HB9QmSEmSE0Y1gsXhQ3Dolesg/w640-h404/Screenshot%202023-05-06%20at%205.58.02%20PM.png" width="640" /></a></div>In the <b>Label</b> Card, we have the option to only show labels when a branch is selected or highlighted. This is neat, saving some effort of creating action filters.<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVBKQ8E6CgcwSZ4t7EJKv38GbU0jtHBOhYXCLeVvv1g8V3mcokoXwIDObT2FW74oUzrAU9jnJuGnzSDcRfWoznMBTP8CiYAESvM9rvz1ovnAf92QhpkccTbqxEGWd5pjndcjIhv7oEwvbm4Mh18M_WYeylxWSS5tmiW9SDRWpNu-TPzbT7eFokS2XkUg/s279/Screenshot%202023-05-06%20at%205.58.50%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="279" data-original-width="265" height="279" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgVBKQ8E6CgcwSZ4t7EJKv38GbU0jtHBOhYXCLeVvv1g8V3mcokoXwIDObT2FW74oUzrAU9jnJuGnzSDcRfWoznMBTP8CiYAESvM9rvz1ovnAf92QhpkccTbqxEGWd5pjndcjIhv7oEwvbm4Mh18M_WYeylxWSS5tmiW9SDRWpNu-TPzbT7eFokS2XkUg/s1600/Screenshot%202023-05-06%20at%205.58.50%20PM.png" width="265" /></a></div><h4 style="text-align: left;">5. Further expectations</h4><div>Funnel chart is an important chart. It can be derived from multi-level Sankey chart. This can be done with a few more hiding options and cascading. The hiding is for a subset of members in a sidebar level/dimension.<div><h4 style="text-align: left;">6. Summary</h4><div>Overall, the new Sankey chart type is easy to use. I am very happy about its pilot trial.</div><div><br /></div><div>With a few additional tweaks, I think it can be applied to a wider area of use cases such as multi-level Sankey chart and Funnel chart.</div></div></div><div><br /></div><div>The above example charts <a href="https://public.tableau.com/app/profile/alex.mou2116/viz/SankeyChartTypeEvaluation/Dashboard1#1" target="_blank">can be viewed</a> on Tableau Public and explored in Web Edit. But you can not download it because the pilot is in web edit only.</div><div><br /></div><div>Follow up post: <a href="https://vizdiff.blogspot.com/2023/05/multi-level-sankey-with-new-sankey.html" target="_blank">Multi-Level Sankey Chart via the New Chart Type in Tableau</a></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-31214545536923268152023-04-03T01:59:00.006-07:002023-09-14T01:19:23.082-07:00Visualizing Cross-Tab Tables with Column Sort<div class="separator" style="clear: both; text-align: center;"><br /></div><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4tCj61saieTFqCwqf0tfO4f3NaGWx143Qk9cPLKVQWLDNO4pNzLcTK0VYC7vu-cIDPQOcL5Gz41x9BNlp93_qfbV4KeN4jIZlJzDXUHcIMxEhyDz0S-7RiNfoKuXYNv-7P_pMofJn7lLpE8N47qG6NmnxHhtEkFTcWc_MZUidz1BvMdvsEGK1Mhpk9g/s995/Hi%20Tech%20Compare.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="646" data-original-width="995" height="416" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi4tCj61saieTFqCwqf0tfO4f3NaGWx143Qk9cPLKVQWLDNO4pNzLcTK0VYC7vu-cIDPQOcL5Gz41x9BNlp93_qfbV4KeN4jIZlJzDXUHcIMxEhyDz0S-7RiNfoKuXYNv-7P_pMofJn7lLpE8N47qG6NmnxHhtEkFTcWc_MZUidz1BvMdvsEGK1Mhpk9g/w640-h416/Hi%20Tech%20Compare.gif" width="640" /></a></div></div><div class="separator" style="clear: both; text-align: justify;"><span style="text-align: left;">Just came across a report by Reuters on </span><a href="https://www.reuters.com/breakingviews/google-giggles-china-techs-shrinking-act-2023-03-31/" style="text-align: left;" target="_blank">USA-China gap widens between respective internet giants</a><span style="text-align: left;">. </span><span style="text-align: left;">The report includes a text table. The caption says the table columns can be sorted. But it is a static image. </span><span style="text-align: left;">(They retracted the table after I reported the issue.)</span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQlY5DB719sJPCS9B_RA-feL5yqYNvCkpLYSUPp2k-unGD4V7y1E3uxTOMJ7i_h1ogPURYrnlMGupQT6iCd_AZjrRC0ox0ZiVxIs82inA0RzYJIsD3Ad8VTsP_fmU5ScpcVQcIhEO1sYKGoX6PKPMY06-T8LmtDsEdkhEpSWCilUc1Sy_L1yOvneYESg/s1484/Economies%20of%20Scale.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1340" data-original-width="1484" height="578" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQlY5DB719sJPCS9B_RA-feL5yqYNvCkpLYSUPp2k-unGD4V7y1E3uxTOMJ7i_h1ogPURYrnlMGupQT6iCd_AZjrRC0ox0ZiVxIs82inA0RzYJIsD3Ad8VTsP_fmU5ScpcVQcIhEO1sYKGoX6PKPMY06-T8LmtDsEdkhEpSWCilUc1Sy_L1yOvneYESg/w640-h578/Economies%20of%20Scale.png" width="640" /></a></div>It picked my interest. I therefore created a Tableau version with column-wise horizontal bar charts and column sort. I would like to show that we can have a more visual way of displaying tables. And I still included a text mode just for the comparison. Or if the user wishes, he can switch between text and visual modes.<div><br /></div><div>Feel free to <a href="https://public.tableau.com/app/profile/a.m.5517/viz/Hi-TechCompanyComparisonUSAvsChina/Dashboard1?publish=yes" target="_blank">download the workbook</a> and look into the details.</div><div><br /></div><div>The key steps in this design are:</div><div><ol style="text-align: left;"><li>Dual axis for each column to show both bars and numbers. This way both the company names and numbers are well aligned.</li><li>Use parameterized metric for column sorting.</li><li>Use parameter action for selecting the column sort. Each column header is a sheet. It includes a number as a dimension for assigning to the parameter.</li><li>Deselect the column header after clicking.</li></ol><div>The deselect method is based on <a href="https://t.co/i4aDT0POUx" target="_blank">a technique introduced by Yuri Val</a>. </div></div><div><div><br /></div><div>That's how we can visualize a cross-tab table with column sort.</div><div><br /><p><br /></p></div></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-66190055919565190492023-03-22T01:46:00.003-07:002023-10-16T12:09:51.132-07:00A Couple of Mismatches in Tableau's Map<p>In the process of creating a dashboard on the <a href="https://public.tableau.com/views/USTravelAdvisory2023/Dashboard1?:language=en-US&:display_count=n&:origin=viz_share_link" target="_blank">US Travel Advisory 2023</a>, I found some mismatches in a few regions in two countries.</p><h3 style="text-align: left;">Gaza Strip</h3><p>One is Gaza Strip in Palestinian Territories. In the latter, there are two regions: Gaza Strip and West Bank. However in Tableau's map, the corresponding polygon to Gaza Strip doesn't show up. I later found out that the region is named "Palestinian Territories" which is the same as the country. So the pair country/province is like "Palestinian Territories/Palestinian Territories". This gives me the correct polygon for Gaza Strip. I noticed that Gaza is a city within Gaza Strip and also the name of cities in multiple countries.</p><p>I expected the correct pairing is "Palestinian Territories/Gaza Strip". I checked openstreet map whose map is used in Tableau. In that map, the region is called Gaza Strip.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfQJfLUflVPDVGlmjBY9fOmPx7KA2fi7NoJg3OJRlhJ0P1RRLFH_IkuHosQazgQtR4W-bCmk43103lj0ThnXoHn6kLQUz7mQd4EumnG-EZA26_zqUQieylBSrJspQ7APBEiCQ5OpTMbaVjw69OgRQdqOeVGdIiwJWaaags2xhTZ9Xjgv_TKMvH6ZJSTg/s656/Screenshot%202023-03-22%20at%201.28.09%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="413" data-original-width="656" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjfQJfLUflVPDVGlmjBY9fOmPx7KA2fi7NoJg3OJRlhJ0P1RRLFH_IkuHosQazgQtR4W-bCmk43103lj0ThnXoHn6kLQUz7mQd4EumnG-EZA26_zqUQieylBSrJspQ7APBEiCQ5OpTMbaVjw69OgRQdqOeVGdIiwJWaaags2xhTZ9Xjgv_TKMvH6ZJSTg/s16000/Screenshot%202023-03-22%20at%201.28.09%20AM.png" /></a></div><h3 style="text-align: left;">Mexico City</h3><p>Mexico City as the capital is like Washington DC and is regarded as a state in Mexico. In mapping Mexico at the state level, I found that Mexico City is not showing up. After some research, I found that I need to use "Distrito Federal" for it to show up. In <a href="https://www.britannica.com/place/Federal-District-Mexico" target="_blank">Britannica</a>, it is stated that Mexico City is officially equivalent to the "Distrito Federal". </p><p>I expected that Mexico City or CDMX work as well as Distrito Federal. It seems that CDMX is replacing Distrito Federal as the official name of the capital.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtaajsyzgK4Vlp-asFG8Z9Pv1FnSyul1Ltb--85HvBhXmKs2RC9r8oVuUSHbNLWQBAf6ON2hI4NvLFLhP2bYlpQcVyBrh8a8javExIDNIYdqf0b56NvzTw6zSWRbZQ213oM8YMZBKd8yumx_k1V8trLYSfV1FLAc7kVsLexQq6ctTRnwcfLoAszrKQoA/s627/Screenshot%202023-03-22%20at%201.35.31%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="593" data-original-width="627" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhtaajsyzgK4Vlp-asFG8Z9Pv1FnSyul1Ltb--85HvBhXmKs2RC9r8oVuUSHbNLWQBAf6ON2hI4NvLFLhP2bYlpQcVyBrh8a8javExIDNIYdqf0b56NvzTw6zSWRbZQ213oM8YMZBKd8yumx_k1V8trLYSfV1FLAc7kVsLexQq6ctTRnwcfLoAszrKQoA/s16000/Screenshot%202023-03-22%20at%201.35.31%20AM.png" /></a></div><h3 style="text-align: left;">Conclusion</h3><p>I hope the above can be fixed to reduce the confusion by Tableau users.</p><p>Below's the <a href="https://travel.state.gov/content/travel/en/traveladvisories/traveladvisories.html/" target="_blank">US Department of State's Travel Advisories</a> visualized in Tableau. The interactive version is <a href="https://public.tableau.com/views/USTravelAdvisory2023/Dashboard1?:language=en-US&:display_count=n&:origin=viz_share_link" target="_blank">here</a>. It's dated on 3/14/2023. This is a dynamic map which needs to be updated all the time. Please refer to the US Department site for the latest.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCN_FMUP1UYoW2MsHHtIhYZupXsWomT4dxzbqsvfPl4eGsmRB1UPiO1hYkdU2Z-TOZA099XZqc04O3RNAgbUKCjTeqG0XikT25VJd1rhyphenhyphenHoV_a0Gta-6oq5ORbOy6xM2xKKfnlpn_rtOa1XKWHi86MyGKKhb0V30QuIjc2-t3WDS7jo5tTShDmFpx86IL1/s3799/Dashboard%202-2.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="3799" data-original-width="799" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhCN_FMUP1UYoW2MsHHtIhYZupXsWomT4dxzbqsvfPl4eGsmRB1UPiO1hYkdU2Z-TOZA099XZqc04O3RNAgbUKCjTeqG0XikT25VJd1rhyphenhyphenHoV_a0Gta-6oq5ORbOy6xM2xKKfnlpn_rtOa1XKWHi86MyGKKhb0V30QuIjc2-t3WDS7jo5tTShDmFpx86IL1/s16000/Dashboard%202-2.png" /></a></div><br /><p><br /></p><p><br /></p>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-63718930299931896152023-01-16T01:43:00.004-08:002023-01-16T18:53:53.234-08:00Converting Date to Fiscal Calendar Date in Excel<p>This is a follow up post to <a href="https://vizdiff.blogspot.com/2023/01/5-4-4-fiscal-calendar-calculations.html" target="_blank">Fiscal Calendar Calculations Cheatsheet for Tableau</a>.</p><p>Excel is a very important tool for data analysis and calculations. It's also an important data repository for Tableau. Some of the calculations can be made in Excel before the data is loaded into Tableau or other tools.</p><p>So here we are going to show the conversion from a regular date to a week-based financial calendar date, via Excel formula. We still use Apple's 5-4-4 Fiscal Calendar as an example.</p><h3 style="text-align: left;">The First Week of a Fiscal Year</h3><p>It is the week that includes October 1st which is an Anchor Date. From the first week, we can determine the Fiscal Year.</p><h3 style="text-align: left;">Fiscal Year</h3><div>Here is the formula for calculating Fiscal Year.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIDJc2sGntw7XjxJrFL3JpWJzAz2kKYB-Xq1IpD0dLmWUNALcOCLhkYfrJJj0pJQWFKn9O6fFgZ0zp2gK9jG1fbUXv5qOryOnb0KAbYp6Dg0i9t6ef3R28kKwJtz2llD7LlE07XZxN6B8xQCCa1xks9ZtX_pahQqeWYMIOemwHiw72RLdW3bCvs8gz-A/s608/Screen%20Shot%202023-01-14%20at%2011.39.16%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="91" data-original-width="608" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIDJc2sGntw7XjxJrFL3JpWJzAz2kKYB-Xq1IpD0dLmWUNALcOCLhkYfrJJj0pJQWFKn9O6fFgZ0zp2gK9jG1fbUXv5qOryOnb0KAbYp6Dg0i9t6ef3R28kKwJtz2llD7LlE07XZxN6B8xQCCa1xks9ZtX_pahQqeWYMIOemwHiw72RLdW3bCvs8gz-A/s16000/Screen%20Shot%202023-01-14%20at%2011.39.16%20PM.png" /></a></div>Note that B2-WEEKDAY(B2)+1 is the first day of the fiscal year. From this day on, the new fiscal year starts.<h3 style="text-align: left;">The First Day of the Fiscal Year</h3><div>In the above Fiscal Year calculation, we used B2-WEEKDAY(B2)+1 to get the first day of the week that includes date B2. This is equivalent to DATETRUNC('week', B2) function in Tableau.<br /><h3 style="text-align: left;">Leap Fiscal Year</h3><div>A common fiscal year has 52 weeks and a leap fiscal year has 53 weeks. We will use this property to determine if a fiscal year is a leap year or not.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgY5HwamwZDkbhi6sVvfpTWhpNlU8Q_y-324K9kK1elrM6pFRn4NK8AWnX-_bgEJrBRr4k1aOFZevtUNxQIeFAje9II8N-7Ri35_jSs0yrGbpAsmVcEobTK8an7Qc2La7ukC-jIcAHq2h4Rxn9yUubpVaANMtnwwjs7rQ8o8-rrx6cekH6OLNkopGqpOA/s611/Screen%20Shot%202023-01-15%20at%2012.27.25%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="90" data-original-width="611" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgY5HwamwZDkbhi6sVvfpTWhpNlU8Q_y-324K9kK1elrM6pFRn4NK8AWnX-_bgEJrBRr4k1aOFZevtUNxQIeFAje9II8N-7Ri35_jSs0yrGbpAsmVcEobTK8an7Qc2La7ukC-jIcAHq2h4Rxn9yUubpVaANMtnwwjs7rQ8o8-rrx6cekH6OLNkopGqpOA/s16000/Screen%20Shot%202023-01-15%20at%2012.27.25%20AM.png" /></a></div><div class="separator" style="clear: both; text-align: left;">This formula allows us to get the total number of weeks between the start dates of two consecutive fiscal years.</div><p style="clear: both; text-align: left;">The formula can be simplified a little: (E2-WEEKDAY(E2)-(D2-WEEKDAY(D2))/7</p><div class="separator" style="clear: both; text-align: left;">The logic test can be written as E2-WEEKDAY(E2)-(D2-WEEKDAY(D2)=371. If true, then it is a leap fiscal year.</div><h3 style="clear: both; text-align: left;">Fiscal Week</h3><div class="separator" style="clear: both; text-align: left;">The fiscal week of a common date is its distance to the start week of the fiscal year.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjiiRHEspKSAhdwV1H2n2wxp4wVgVno6Z2SmREuoTY1kXHlpWSoRZYo_qqg4o3kLi9QK36QMkH24g5qvTZvgGY2j-Yf10FdRZZ8tjCYNNQBPbMtbaLKO0JBacTfHd6kODLLonrFGAjeVoNTdb1_5PrVkwjQc3dDj4EMj0jtbkZsRPnxQwSlcoSuWvsBg/s610/Screen%20Shot%202023-01-15%20at%2012.30.59%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="91" data-original-width="610" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjiiRHEspKSAhdwV1H2n2wxp4wVgVno6Z2SmREuoTY1kXHlpWSoRZYo_qqg4o3kLi9QK36QMkH24g5qvTZvgGY2j-Yf10FdRZZ8tjCYNNQBPbMtbaLKO0JBacTfHd6kODLLonrFGAjeVoNTdb1_5PrVkwjQc3dDj4EMj0jtbkZsRPnxQwSlcoSuWvsBg/s16000/Screen%20Shot%202023-01-15%20at%2012.30.59%20AM.png" /></a></div><h3 style="clear: both; text-align: left;">Fiscal Month/Period</h3><div>In a common fiscal year, we will have 5-4-4 weeks for each of the 4 fiscal quarters. In a leap fiscal year, we will have 5-4-5 weeks in the first fiscal quarter. That is, the 3rd fiscal month/period will have 5 weeks in a leap fiscal year instead of 4.</div><div><br /></div><div>Here is the formula for calculating fiscal month/period</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnqpwbfhSkD8v087iUIUS9pqPcQOU0SPQrD63PPLebNOJkUCEvJ8IEjGyY-Uc56GXtbl_wsWDR2jzZYmdCxPUlIn7beXNiCgZ5VuT01U9MxfEKflMp2YaESx-fbfjYjJOClNSMxkOsHM3ONoXm_1K2cW3Tfx1r0l8Npxhe1vqxnsVWQp_AJoJ-MhvU0w/s608/Screen%20Shot%202023-01-15%20at%2012.34.44%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="91" data-original-width="608" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhnqpwbfhSkD8v087iUIUS9pqPcQOU0SPQrD63PPLebNOJkUCEvJ8IEjGyY-Uc56GXtbl_wsWDR2jzZYmdCxPUlIn7beXNiCgZ5VuT01U9MxfEKflMp2YaESx-fbfjYjJOClNSMxkOsHM3ONoXm_1K2cW3Tfx1r0l8Npxhe1vqxnsVWQp_AJoJ-MhvU0w/s16000/Screen%20Shot%202023-01-15%20at%2012.34.44%20AM.png" /></a></div><h3 style="text-align: left;">Fiscal Quarter</h3><div>It's easy to create the formula for getting fiscal quarters from fiscal months.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeJDKDaaMW9ZyLdRzkUncumpu7m4nZo0T2oA_H4FHmj0TVhNA3MKRghEIiSYeq3ggU7fVinlBkChzKcCPGYL4KKxgl7uOy0GHimztBvL1egcQCYteDwjtg3CFm8ynIwTn56RyZQbExqNsRuqKWNLhQNV6Dm6tzqc8aKqeiDkGunjW9jgBHMgHQqZkdEA/s611/Screen%20Shot%202023-01-15%20at%2012.40.25%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="92" data-original-width="611" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgeJDKDaaMW9ZyLdRzkUncumpu7m4nZo0T2oA_H4FHmj0TVhNA3MKRghEIiSYeq3ggU7fVinlBkChzKcCPGYL4KKxgl7uOy0GHimztBvL1egcQCYteDwjtg3CFm8ynIwTn56RyZQbExqNsRuqKWNLhQNV6Dm6tzqc8aKqeiDkGunjW9jgBHMgHQqZkdEA/s16000/Screen%20Shot%202023-01-15%20at%2012.40.25%20AM.png" /></a></div><h3 style="text-align: left;">Demo File for Download</h3><div>We have <a href="https://docs.google.com/spreadsheets/d/1RcSbfqCq9zSemCQuYdMUHowjoVpwbZdM/" target="_blank">an Excel file</a> for you to download (Click link and open it in Google spreadsheet. Go to File and click Download.) It has all the above formulas and all the fiscal years in the next 1000 years. Feel free to use it in your data analysis.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfUH5J8e0SpJDjeBZLpw7Q5LnymlyOsZkCpU6LPzsJZVKevCxDFxk97wYZvxa5agQDDgQcC0YTjJu98B7PyU9qPdEEZu2gnuRD6ZgUwF5QUKEbKiIkKotbu1IOdjwFEBkRRxmNcBwaUlUH0sD8Zh4JwhhIi-UQeGdPdJZTKELaGZCU9BMOlSEAW0Y53Q/s825/Screen%20Shot%202023-01-16%20at%201.20.47%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="571" data-original-width="825" height="442" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhfUH5J8e0SpJDjeBZLpw7Q5LnymlyOsZkCpU6LPzsJZVKevCxDFxk97wYZvxa5agQDDgQcC0YTjJu98B7PyU9qPdEEZu2gnuRD6ZgUwF5QUKEbKiIkKotbu1IOdjwFEBkRRxmNcBwaUlUH0sD8Zh4JwhhIi-UQeGdPdJZTKELaGZCU9BMOlSEAW0Y53Q/w640-h442/Screen%20Shot%202023-01-16%20at%201.20.47%20AM.png" width="640" /></a></div><h3 style="text-align: left;">Conclusion</h3></div><div>With the above formulas, we save the dependency on a database table for converting a common date to fiscal date. The above example is based on a 5-4-4 week-based fiscal calendar. It may not match exactly your fiscal calendar. Nonetheless it may give you some ideas for creating your conversion formulas.</div><div><br /></div><div>Feel free to leave comments or contact me at <a href="https://twitter.com/aleksoft" target="_blank">@aleksoft</a> at twitter.</div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-70568066180179949462023-01-08T02:26:00.017-08:002023-01-17T18:05:02.421-08:00Converting Date to Fiscal Calendar Date in Tableau<p>Week-based calendars are used in many companies as their fiscal calendars. The total weeks in a fiscal year is 52 weeks, that is, 364 days. Each quarter has 13 weeks. There are 3 varieties of 13 weeks: 5-4-4, <a href="https://nrf.com/resources/4-5-4-calendar" target="_blank">4-5-4</a> and <a href="https://en.wikipedia.org/wiki/4–4–5_calendar" target="_blank">4-4-5</a> weeks per quarter. In leap years, there are 53 weeks or 371 days. IRS has created <a href="https://www.irs.gov/publications/p538#en_US_202112_publink1000270584" target="_blank">special code</a> for the 52-53-week fiscal years.</p><p>Each company's week-based fiscal calendar will differ in 4 ways:</p><div style="text-align: left;">- define the 13-week quarter as 5-4-4, 4-5-4 or 4-4-5<br />- define its own start week <br />- define its own way of placing the leap week</div><div style="text-align: left;">- define the first day of week as Sunday, Monday etc.</div><div style="text-align: left;"><br /></div><div style="text-align: left;">Please refer to each company's official fiscal calendar for the exact design. This post is just a reference for the conversion of a common date to fiscal dates under given rules.</div><div style="text-align: left;"><p><a href="https://www.google.com/search?client=safari&rls=en&q=Apple+Fiscal+Calendar&ie=UTF-8&oe=UTF-8" target="_blank">Apple's Fiscal Calendar</a> is widely disseminated on the web. Here we try to clarify some of the misunderstandings and provide calculation assistance in Tableau for analyzing time series based on the 5-4-4 format. We will use Apple's fiscal calendar as an example below.</p></div><h3 style="text-align: left;">Common Fiscal Year</h3><p>In a common year, it's a 52-week year which has 52x7 = 364 days.</p><p>A year has 4 quarters and each quarter has 3 fiscal periods/months. </p><p>Each quarter has 13 weeks.</p><p>At Apple, the first period of a quarter has 5 weeks and the other two have 4 weeks each. It's thus called a 5-4-4 calendar.</p><h3>First Week of a Fiscal Year</h3><p>For a week-based calendar, it is very important to understand the definition of its first week. Each company may have a different definition of the first week.</p><p>For Apple's fiscal calendar, its first week is the one that always includes October 1st. This week is the first one of the next fiscal year. For example, the week that includes 10/1/2022 is the first week of fiscal year 2023.</p><h3>First Day of a Week</h3><p>In Apple's fiscal calendar it's always a Sunday. There may be other conventions where we need to change the following calculations.</p><h3 style="text-align: left;">Leap Rule</h3><p>Every 6 years or so, there will be a leap fiscal year. </p><p>A leap fiscal year will have 53 weeks, one more week than the common fiscal year. </p><p>The cause of the leap fiscal year is as follows:</p><p>- A common fiscal year has 364 days while a common calendar year has 365 days. It is 1 day less than the calendar year. It seems that every 7 years, we need to add a week to the common fiscal year to realign the starting dates of fiscal years. But the 7-year frequency is broken by the leap calendar years because a calendar year doesn't always have 365 days. This makes it a lot less predictable when guessing which year is a leap year.</p><p>- A calendar year has its own <a href="https://en.wikipedia.org/wiki/Leap_year" target="_blank">leap year</a> which has 366 days. This happens on every 4 years on the years divisible by 4 and excluding the years that can be divided by 100 but can't be divided by 400. </p><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFayBJahljhySylQRALssx0V_F2WJSZGsfhyTo07fseY6WOl9F_arm8H3hboJOw-BXgz0FSz3-EMe1zdnQWil9Ud4ccfzUkZyNXBliqAPPvt_KPu77NUXRTwUfvU3SB0L9AH2b5rKzkY_iRTDHlahNZJJWDmXGELCzmuoaPPn5u0jamBcWcteHRdmrAA/s479/Screen%20Shot%202023-01-08%20at%202.30.09%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="479" data-original-width="308" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjFayBJahljhySylQRALssx0V_F2WJSZGsfhyTo07fseY6WOl9F_arm8H3hboJOw-BXgz0FSz3-EMe1zdnQWil9Ud4ccfzUkZyNXBliqAPPvt_KPu77NUXRTwUfvU3SB0L9AH2b5rKzkY_iRTDHlahNZJJWDmXGELCzmuoaPPn5u0jamBcWcteHRdmrAA/s16000/Screen%20Shot%202023-01-08%20at%202.30.09%20AM.png" /></a></div></div><div class="separator" style="clear: both; text-align: center;"><i>The leap years in the next 100 years and the gaps in between.</i></div></div><h3 style="text-align: left;">Leap Week</h3><p>In a leap fiscal year, there will be an extra week or a leap week. Where to place it depends on the company. In Apple's fiscal calendar, it's added in the third fiscal month which becomes a 5-week month during a leap fiscal year. That is, we insert the leap week after the 13th week of the fiscal year. Or the 14th week of a leap fiscal year is the leap week.</p><h3>Placement of the Leap Week</h3><p>We have an extra week in a leap fiscal year. It is appended to the end of the first fiscal quarter or the 3rd fiscal month/period. Thus, the first fiscal quarter, instead of 5-4-4 quarter and having 13 weeks, it becomes 5-4-5 quarter and having 14 weeks.</p><h3 style="text-align: left;">How to Determine if a Fiscal Year is a Leap Year?</h3><p>This is easy. Just find out if October 1st in two consecutive years has a gap of 53 or 52 weeks. If 53 weeks, it is a leap year for sure. So, use this logical formula:</p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_6zGoi3sMTksdu-FIE3xahCTGRNRe-D0UaDYCt2_ggGMCR9bndgXTQ56qH2e6VUoYZum1vWpuV-P-5qdDl0sayoKnYK0Af_w5tWYIpkduTNValPoVupwFY-_HqGtods_E2UIHiP1LTfIe7NUwuZoZojC7sDn7aleZulp0ifshHLJ5RNyTlhIOSGsaFQ/s593/Screen%20Shot%202023-01-07%20at%203.30.34%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="100" data-original-width="593" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi_6zGoi3sMTksdu-FIE3xahCTGRNRe-D0UaDYCt2_ggGMCR9bndgXTQ56qH2e6VUoYZum1vWpuV-P-5qdDl0sayoKnYK0Af_w5tWYIpkduTNValPoVupwFY-_HqGtods_E2UIHiP1LTfIe7NUwuZoZojC7sDn7aleZulp0ifshHLJ5RNyTlhIOSGsaFQ/s16000/Screen%20Shot%202023-01-07%20at%203.30.34%20PM.png" /></a></div><p></p><h3>The First Day of a Fiscal Year</h3><p>The Sunday of the first week is the first day of the fiscal year.</p><p>In Tableau formula, we can easily derive the first day of a fiscal year. For example for the fiscal year 2023, the first day is:</p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p style="text-align: left;"><span style="font-family: courier;"><span style="color: #0b5394;">DATETRUNC</span>('week', <span style="color: #0b5394;">MAKE<span>DATE</span></span>(<span style="color: #ffa400;">FiscYear</span>-1,10,1))</span></p></blockquote><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><p></p></blockquote><div style="text-align: left;"><span style="font-family: inherit; font-weight: normal;"><a href="https://qonversion.io/blog/apple-fiscal-calendar-2023/" target="_blank">Some says</a> the first day is always the last Sunday of September. This is not exact. It's only about 6/7 correct. It's the Sunday within Sept 25th - Oct 1st. </span></div><div><h3>The Last Day of a Fiscal Year</h3></div><div>It's actually the last Saturday in September. For any FiscYear, the last day is the first day of the next FiscYear minus one.</div><div><br /></div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div style="text-align: left;"><span style="color: #0b5394; font-family: courier;">DATETRUNC</span><span style="font-family: courier;">('week', </span><span style="color: #0b5394; font-family: courier;">MAKE<span>DATE</span></span><span style="font-family: courier;">(<span style="color: #ffa400;">FiscYear</span>,10,1))-1</span></div></blockquote><h3 style="text-align: left;">Convert a Date to Fiscal Year/Quarter/Month/Week?</h3><div style="text-align: left;">Here are the Tableau formula we can use to convert a regular date to its corresponding fiscal date. Note that they are dependent on the ones above them.</div><div style="text-align: left;"><br /></div><div style="text-align: left;">- What fiscal year is it in? We got <span style="color: #ffa400;">FiscYear</span> which is dependent on <span style="color: #ffa400;">Order Date</span> alone.</div><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px; text-align: left;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_zWBoOakcUFd_f6C2qB3rN4ol2tsh1_810T-4uVl_PNW2dobauJS3nk2drRY6w3KQalWQrRNwqJ6R4gnghp5yJXN5k6fcJqfPIfaAF58pk6vkEFO432M1Gb09IkAHHvIkwCcxFnAO3qvEiXSi7ejNUfobSOr_tP8vmagj8TnJDEPE-USUshgRt6P15g/s588/Screen%20Shot%202023-01-07%20at%203.22.55%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="198" data-original-width="588" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj_zWBoOakcUFd_f6C2qB3rN4ol2tsh1_810T-4uVl_PNW2dobauJS3nk2drRY6w3KQalWQrRNwqJ6R4gnghp5yJXN5k6fcJqfPIfaAF58pk6vkEFO432M1Gb09IkAHHvIkwCcxFnAO3qvEiXSi7ejNUfobSOr_tP8vmagj8TnJDEPE-USUshgRt6P15g/s16000/Screen%20Shot%202023-01-07%20at%203.22.55%20PM.png" /></a></div></blockquote><p>- What fiscal week is it in? We got <span style="color: #ffa400;">FiscWeek</span> which is dependent on <span style="color: #ffa400;">Order Date</span> and <span style="color: #ffa400;">FiscYear</span>.</p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGbcJqseo-RHAxNz6_eRTYsiClyW4jLLTwh5fsQVMdeo9fyuhPrvYIh2NE-c-anAkrx18G_05nX2GYxcrao5lzN6n_xCbHrq6-pEH5DvbgwlfnzRmjeik1g-cT84mjDlcMZ5AHibx_eLzBwlj6fnyaEzec7Xy5X8JEBRz1Fd0reFPwCf-K2hd821vZ-g/s502/Screen%20Shot%202023-01-07%20at%203.23.46%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="88" data-original-width="502" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiGbcJqseo-RHAxNz6_eRTYsiClyW4jLLTwh5fsQVMdeo9fyuhPrvYIh2NE-c-anAkrx18G_05nX2GYxcrao5lzN6n_xCbHrq6-pEH5DvbgwlfnzRmjeik1g-cT84mjDlcMZ5AHibx_eLzBwlj6fnyaEzec7Xy5X8JEBRz1Fd0reFPwCf-K2hd821vZ-g/s16000/Screen%20Shot%202023-01-07%20at%203.23.46%20PM.png" /></a></div></blockquote><p>- What fiscal month (period) is it in? We got <span style="color: #ffa400;">FiscMonth</span> which is dependent on <span style="color: #ffa400;">FiscWeek</span> and <span style="color: #ffa400;">Leap Year T|F</span>. (Credit <a href="https://twitter.com/StatHunting" target="_blank">Steve Fenn</a>) </p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCM1FTSNVhAaC5GPY1pTTjbXY6k2YJgem-Arn7ogZfqg49gJqsA1WS2nhQnl5Xm1qQ9qddGKKYm3K9bll_rbUGVWm-WIul7SXL2NeK5JKcuJu9Wpyo4a71cCfpFZZJsp1H_M1HeKEkrSryDeWpWXl7jMU7xKir_Vbp7oH-HhC3qYdakZRFCcPTjSHHqw/s563/Screen%20Shot%202023-01-07%20at%203.31.31%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="151" data-original-width="563" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgCM1FTSNVhAaC5GPY1pTTjbXY6k2YJgem-Arn7ogZfqg49gJqsA1WS2nhQnl5Xm1qQ9qddGKKYm3K9bll_rbUGVWm-WIul7SXL2NeK5JKcuJu9Wpyo4a71cCfpFZZJsp1H_M1HeKEkrSryDeWpWXl7jMU7xKir_Vbp7oH-HhC3qYdakZRFCcPTjSHHqw/s16000/Screen%20Shot%202023-01-07%20at%203.31.31%20PM.png" /></a></div></blockquote><p>- What fiscal quarter is it in? We got <span style="color: #ffa400;">FiscQuarter</span> which is dependent on <span style="color: #ffa400;">FiscMonth</span>.</p><blockquote style="border: none; margin: 0px 0px 0px 40px; padding: 0px;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAvhCi89mFuvdM5cQbF-blZkJD1dOPN03TdgmEwCNOBAvCDu3rOxuNjTvFIhA5F9hbzfKg2qwrZLW9rsUF2UynGX40tr7hUQ1h44BCbwKYU9rqUfRWS9CXC3Cafr1rF5tcW9HfSukw3jK0DIN1cG3nw8KJYgZOmH8jIqZBXBb4HBMD806NzCxeI-uryw/s364/Screen%20Shot%202023-01-07%20at%203.25.24%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="90" data-original-width="364" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjAvhCi89mFuvdM5cQbF-blZkJD1dOPN03TdgmEwCNOBAvCDu3rOxuNjTvFIhA5F9hbzfKg2qwrZLW9rsUF2UynGX40tr7hUQ1h44BCbwKYU9rqUfRWS9CXC3Cafr1rF5tcW9HfSukw3jK0DIN1cG3nw8KJYgZOmH8jIqZBXBb4HBMD806NzCxeI-uryw/s16000/Screen%20Shot%202023-01-07%20at%203.25.24%20PM.png" /></a></div></blockquote><h3>Conclusion</h3><p><span style="font-family: inherit;">Note that each company may have its own way of defining its week-based fiscal calendar. Hope that this post can give you some ideas for deriving your own specific formula.</span></p><p><span style="font-family: inherit;">Feel free to download <a href="https://public.tableau.com/views/5-4-4FiscalCalendarCalculations_16731719508300/Dashboard1?:language=en-US&:display_count=n&:origin=viz_share_link" target="_blank">this workbook</a> and copy over the formula if you wish. Contact <a href="https://twitter.com/aleksoft" target="_blank">@aleksoft</a> if questions. </span></p>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-56355079686555050462022-10-28T02:31:00.003-07:002022-10-28T20:31:03.581-07:00Resolving Simpson's Paradox: The Whole vs The Parts<p>Christine suggested me to have a look at <a href="https://en.wikipedia.org/wiki/Simpson%27s_paradox" target="_blank">Simpson's Paradox</a>, following my recent posts on <a href="https://vizdiff.blogspot.com/2022/10/rendering-anscombes-quartet-in-tableau.html" target="_blank">Anscrombe's Quartet</a> and <a href="https://vizdiff.blogspot.com/2022/10/recreating-datasaurus-dozen-in-tableau.html" target="_blank">Datasaurus Dozen</a>. They are all about learning to look at statistics in an impartial way.</p><p>Simpson's Paradox is about the difference between the stats of an entire data set and the stats of the same data set sliced by a dimension. They can be quite different or even contradictory. We can't take one for the other.</p><p>We are going to show some visualization techniques to compare the whole vs the parts through two examples.</p><h3 style="text-align: left;">UC Berkeley Admission Gender Bias</h3><p>The data is from <a href="https://en.wikipedia.org/wiki/Simpson%27s_paradox" target="_blank">here</a>. From the campus total percentage, we see that the admission rate is 39%. Then men's rate is 45% and women's is 30%. So it seems that there is a campus wide bias against women. However at the department level, we see that departments A and B have very high admission rates for women. They are even higher than those for men.</p><p>So it's not enough to just draw conclusions from the stats of the entire school. It may not be fair to some of the departments. We need to look further into the departments. </p><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://public.tableau.com/views/Simpsonsparadox_16669491408960/Dashboard1?:language=en-US&:display_count=n&:origin=viz_share_link" imageanchor="1" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" data-original-height="452" data-original-width="691" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhh1o0lAz4U5fkECc5447sKMohlDIq-Dl3OpnrFy1wJpXUOohe8lbMy64jaCKt8hnNMg2tezMFBy2Ct1aVJfvxW5GBNibHmqVF1DE-O19TaOuU5H6mg38mbFc-R_xKCczO-5xqfMfM_cUrXRItG5rjvvpye7xGxP6bEtLp6L6VrFV9l-DHb-AwA8WpcLQ/s16000/Screen%20Shot%202022-10-28%20at%209.49.05%20AM.png" /></a></div></div><h3 style="text-align: left;">Superstore Sales Trend</h3><p>The similar story goes for sales trend. We can calculate the growth rate trend of sales for the whole business. But we also need to look into the sales trend of every product category, or that of each state. The overall trend may not represent that of a single category or of a single state.</p><p>We can see that copiers and phones sales are growing faster than the overall business. Some are growing in a rate similar to the overall rate. Some are flat and lag behind. </p><p>Because we are comparing only the gradients of sales trend, the dual axis are not synchronized.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://public.tableau.com/views/Simpsonsparadox_16669491408960/Dashboard1?:language=en-US&:display_count=n&:origin=viz_share_link" imageanchor="1" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" data-original-height="569" data-original-width="691" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi8l_hUMJcDxkxJHbodJ01PB8t-WIx8vubZGy4fpk3nl6yKao7BS0cK13-rpruD2vMRFBQ0AzwfgpaoFLVTNP46jQKEwdoeRv0L_BqZ0DNdkgbIskfx01172coyg1UrWfmAYd8puwsJJGpR4Ml-vBdjwFoSEl8CP-bDQRQ-9Bv1_HuYNjK1H7_rk4YQKg/s16000/Screen%20Shot%202022-10-28%20at%209.49.54%20AM.png" /></a></div>Feel free to download <a href="https://public.tableau.com/views/Simpsonsparadox_16669491408960/Dashboard1?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">the workbook</a> and explore it. Leave comments if you have something to share.<br /><p><br /></p>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-82911214560870427642022-10-08T23:02:00.008-07:002022-10-12T21:33:45.784-07:00#TweakThursday: Horizontal vs Vertical Bars<div><a href="https://vizdiff.blogspot.com/search/label/%23TweakThursday" target="_blank">#TweakThursday</a>: From time to time I tweak someone else's public viz and try to make it better to my subjective view.</div><div><br /></div>How does one use horizontal bars and vertical bars? How to order time-based multiples in a trellis chart?<div><br /></div><div>Here are my own rules of thumb:</div><div><ul style="text-align: left;"><li>Vertical bars are for time-based trends.</li><li>Horizontal bars are for categorical comparison.</li><li>Always place the latest cell in a time-based trellis at the top-left corner where the focus is.</li></ul></div><div>I found that Professor Klaus Schulte's recent MakeoverMonday viz submission is quite interesting. I just tweaked it a bit according to the above rules. Hope it makes the viz a bit more interesting.</div><div><br /></div><div>Here is <a href="https://public.tableau.com/views/InternetSpeedAcrossEurope_16652925842930/internetspeedMoM2022-39?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">my resulting viz</a>. Feel free to download it and explore.</div><div><br /></div><div>Note that the vertical axis is the bins for the histogram. We easily see <span style="color: red;">France</span> is placed in higher bins than the other 3 highlighted countries recently. Since the most recent is more interesting than the past, placing the the latest cells at the top left corner makes it easier to check.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://public.tableau.com/views/InternetSpeedAcrossEurope_16652925842930/internetspeedMoM2022-39?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" data-original-height="800" data-original-width="700" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiwefjAGeUTAh6oF9xCk-cSTICXtLInqP9E-Q7TaNFEpkZ4L-1ljO8ZYqNOlnDvFlPlSwKlu9cu0YkRVwzpRrbtK4x1oyhF1yCbqLB3t_1fYWjgg2RV2TUGCDFHtcZ2EGG0vYrbGfn2FmmMp34UEiFOzBT3FeokR7nIfdDR9oSIcp5VDL-2hARuGuyb7A/w560-h640/internet%20speed.jpg" width="560" /></a></div><div class="separator" style="clear: both; text-align: center;"><div style="text-align: left;">Had <a href="https://twitter.com/ProfDrKSchulte/status/1579098873393729536?s=20&t=CbBcIvmwdK_GxKLCWvjPBg" target="_blank">a few exchanges</a> with Klaus in Twitter about my reasoning behind the edit.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://twitter.com/ProfDrKSchulte/status/1579098873393729536?s=20&t=CbBcIvmwdK_GxKLCWvjPBg" imageanchor="1" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" data-original-height="857" data-original-width="596" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgnDzcUa2AjE0eytQ1oIGXQ9bycoNOneichep6dvzzbDDPcPOPgjDwNrRTUYva8Kf0xuGQAXy5QRQoSiH8soPwLRJTLVfUN0MU9-eTTHBZTJTskXNYVYv2BhvfQ_4AoS9oHWyb2fmmHHo2cpl4kIbj8JR12Ph2-kyvSLeu2jx0YcRFSWyaFSZTeZd67qw/s16000/Screen%20Shot%202022-10-10%20at%2012.55.59%20AM.png" /></a></div></div><div>This is <a href="https://public.tableau.com/views/InternetSpeedAcrossEurope/internetspeed?:language=en-US&:display_count=n&:origin=viz_share_link" target="_blank">Professor Klaus Schulte's original viz</a>.</div><div><br /></div><div class="separator" style="clear: both; text-align: center;"><a href="https://public.tableau.com/views/InternetSpeedAcrossEurope/internetspeed?:language=en-US&:display_count=n&:origin=viz_share_link" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" data-original-height="932" data-original-width="800" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj8OK7Z6j8hey-TiigNU123pdN7sXnQ29sit82hDDFKNPYaZvzsgI0QchJtfcMEtKu8Rp5D6TfYAeTDDjUbjF5tA7DY0q4UgifbVzrS-eYrrpSOdtaBiduASw56LW0PPIEle8rLcV0eui6UkIULC--ZMaFkcEXo5UZxfbZo3_08W9PiJTOuenSe7Nxrlw/w550-h640/internet%20speed%20Klaus.jpeg" width="550" /></a></div><br /><div><br /></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-17888024923543861232022-10-07T15:43:00.002-07:002022-10-14T10:19:39.848-07:00Recreating Datasaurus Dozen in Tableau<p></p><div class="separator" style="clear: both; text-align: left;">This post is about 13 data sets, known as <a href="https://www.autodesk.com/research/publications/same-stats-different-graphs" target="_blank">Datasaurus Dozen</a>, that have the same stats and different distributions. Stats can be deceiving while data visualization can makes a big difference.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">Inspired by <a href="https://vizdiff.blogspot.com/2022/10/rendering-anscombes-quartet-in-tableau.html" target="_blank">Anscombe's quartet</a> and <a href="http://www.thefunctionalart.com/2016/08/download-datasaurus-never-trust-summary.html" target="_blank">Alberto Cairo's Datasaurus</a>, Justin Matejka and George Fitzmaurice crafted another 12 datasets which have the same stats and different distributions. Thus the Datasaurus and the Dozen.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">Here I recreated them in a <a href="https://public.tableau.com/views/DataSaurusDozen/DatasaurusAnim?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">Tableau dashboard</a> and calculated dynamically all the summary stats using the native table calculation functions in Tableau. We verified that the 13 datasets have the same stats. But they do visualize differently. Feel free to download it and explore it on desktop.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">I noted that R-Squared is a bit different from each other, while <a href="https://vizdiff.blogspot.com/2022/10/rendering-anscombes-quartet-in-tableau.html" target="_blank">Anscombe's quartet</a>'s R-squared's are much closer to each other.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://public.tableau.com/views/DataSaurusDozen/DatasaurusAnim?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" data-original-height="730" data-original-width="628" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj7hm1NRuY65d_N8OKRzp-ciBTld7Dk2eKSfPdltt2O_WOpOc7AHN_9pWjfNAEoErieVS51995I5NSeKDT1tp5yAJuv0LdT8ZsekwguWYsV8t7FBbHqeo9gEuMsZPT_G8RDDWlO_VkhGf7E-gklab_7DgEmY9ZSxE-QKd9gjEo1hd-gHP2z7fs4udmkyA/w550-h640/ezgif.com-gif-maker.gif" width="550" /></a></div>By the way, it's the first time for me to use the native animation function in Tableau to create this gif. Unfortunately <a href="https://public.tableau.com/shared/QKX49MCB8?:display_count=n&:origin=viz_share_link" target="_blank">Tableau Public</a> can't run this because it says the chart is too complex. If you wish, download it to your desktop where one can view much better visual effect.<p></p><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://public.tableau.com/views/DataSaurusDozen/DatasaurusAnim?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" data-original-height="437" data-original-width="473" height="370" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjxbVYQo1TsmRbFOfknw8KeMpHrz-7se0bVTpSsneapsh_E01HMdmYes303DVksiV2kCZgEUz8aXWHufxpkxnvXKE6IrAGnSum9S2WmuSRn5VqfIE1hfGF_Je0cFDsWab0Q0YIFcdA58UeWk6N2veY9YUaQ3LCW7j-4WT0bmhbU8Y4OXpulNCwC76SJ0w/w400-h370/ezgif.com-gif-maker%20(1).gif" width="400" /></a></div><br /></div>Happy exploring with Tableau!Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-41786605823257205942022-10-04T15:31:00.004-07:002022-10-05T09:57:01.929-07:00Rendering Anscombe's Quartet in Tableau<p><a href="https://en.wikipedia.org/wiki/Frank_Anscombe" target="_blank">Francis Anscombe</a>, a British statistician and a professor at Princeton and Yale, constructed 4 different sets of data which all have the same stats, known as <a href="https://en.wikipedia.org/wiki/Anscombe's_quartet" target="_blank">Anscombe's quartet</a>. However the quartet's data distributions are quite different. </p><p>Stats alone can be deceiving. Through data visualization, we can gain powerful insights into their differences. </p><p>So, I decided to render Anscombe's quartet in Tableau. All calculations are based on Tableau's native functions. Without this exercise, I may never get chances to use some of the statistical functions in Tableau. Hope that this can inspire more people to use them, such as:</p><div style="text-align: left;">Variance: <span style="color: #0b5394;">WINDOW_VAR</span>(<span style="color: #0b5394;">SUM</span>(X))<br />Correlation: <span style="color: #0b5394;">WINDOW_CORR</span>(<span style="color: #0b5394;">SUM</span>(X), <span style="color: #0b5394;">SUM</span>(Y))</div><div style="text-align: left;"><br /></div><div style="text-align: left;">The stats summary is generated dynamically and displayed via annotation.</div><p>Here is the resulting <a href="https://public.tableau.com/views/AnscombesQuartet_16649195794170/AnscombesQuartet?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">dashboard</a>, rendered in a single sheet. Feel free to download it.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://public.tableau.com/views/AnscombesQuartet_16649195794170/AnscombesQuartet?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" style="margin-left: 1em; margin-right: 1em;" target="_blank"><img border="0" data-original-height="899" data-original-width="799" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjWUXPnTrsORP4pA_usbrnyomXHSqtgni9tB-sdpelGFWCdU7rWgnawgGwJuKsmuDvNJXoMJjUmj4age_VpqWNREV7GfLKRiCS_emFgNbfFMtpGxGfcxaSFF5dw6STYUy9GF-jyd6H8YZf5IFakWG8ugsVGX4pIKgu84T-MLRnOVtKBcnjSpZCOeYkMlQ/s16000/Anscombe's%20Quartet.png" /></a></div><p>All the trend lines are also identical after being rounded to two decimals. The trend lines are generated by Tableau based on data. We can see that the R-Squared and P-value are also the same.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKYFYCb__n1PL50Q11C2XSY49ou-ABWOPTNxrsljVxv3Dms-_e38cQrZwtL2ht-F3VXMpzsR0VOVHwQ0zCvpiHbT6yVHVJ0qSP3H7Dq-Q_4sTK9gfRt_kCn4KtxqZXcLdunRfr64vj6DT2FixYH-gl9AA0KmjdLuNsMmBWielzqMe4QimSo3vowh7hQA/s370/Screen%20Shot%202022-10-04%20at%203.00.45%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="365" data-original-width="370" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgKYFYCb__n1PL50Q11C2XSY49ou-ABWOPTNxrsljVxv3Dms-_e38cQrZwtL2ht-F3VXMpzsR0VOVHwQ0zCvpiHbT6yVHVJ0qSP3H7Dq-Q_4sTK9gfRt_kCn4KtxqZXcLdunRfr64vj6DT2FixYH-gl9AA0KmjdLuNsMmBWielzqMe4QimSo3vowh7hQA/s16000/Screen%20Shot%202022-10-04%20at%203.00.45%20PM.png" /></a></div><div class="separator" style="clear: both; text-align: justify;">Here is the quartet's data:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjghWQn3fLVeLpxC4_noQBIDOAULc9-S69-oCTCv5CZlOiApU_dtfHWtbnOIvO-QglQHO82G8-qvx_97lCY6jRzx1n8P80ZM23qLQBccz5Z-S6KMbi_f1m7oZxAyRUYvZnkNp5XiIU6NOpqd1Br931FLUcbm1pKxMQhmL6x1csWLXAw0qbKUYCHHQGM9Q/s451/Screen%20Shot%202022-10-04%20at%203.14.36%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="451" data-original-width="413" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjghWQn3fLVeLpxC4_noQBIDOAULc9-S69-oCTCv5CZlOiApU_dtfHWtbnOIvO-QglQHO82G8-qvx_97lCY6jRzx1n8P80ZM23qLQBccz5Z-S6KMbi_f1m7oZxAyRUYvZnkNp5XiIU6NOpqd1Br931FLUcbm1pKxMQhmL6x1csWLXAw0qbKUYCHHQGM9Q/s16000/Screen%20Shot%202022-10-04%20at%203.14.36%20PM.png" /></a></div><div class="separator" style="clear: both; text-align: justify;">Anscombe wanted to let people know that stats are not enough to characterize a data set. Visualization is important to help us understand data and get more insights into the data. He wrote a 5-page paper in 1973 to stress on using <a href="https://www.jstor.org/stable/2682899?origin=crossref" target="_blank">graphs for statistical analysis</a>.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIBriNnuRMePnFoXbRYvf1iJcaExr-Q6fFzIZOsmxksbTUpmvWwQUHcoViV-iYoneeG0oeXpW9wa5jMk8XJF-RDCWy49ac2i9zo3ZhtYYa7CLbolScE6sWhZRqGw9l50_4vodJJWlQrCee6BHTMv4mzO_ZAEKgVzpnhkZU3pcHbwOVY-CD2me2pznfug/s1045/anscombe%20paper.gif" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1045" data-original-width="760" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhIBriNnuRMePnFoXbRYvf1iJcaExr-Q6fFzIZOsmxksbTUpmvWwQUHcoViV-iYoneeG0oeXpW9wa5jMk8XJF-RDCWy49ac2i9zo3ZhtYYa7CLbolScE6sWhZRqGw9l50_4vodJJWlQrCee6BHTMv4mzO_ZAEKgVzpnhkZU3pcHbwOVY-CD2me2pznfug/s16000/anscombe%20paper.gif" /></a></div>Hope that this helps us better understand the value of data visualization.<br /><div class="separator" style="clear: both; text-align: justify;"><br /></div><br />Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-37753722728682255982022-08-30T23:38:00.002-07:002022-08-31T09:12:49.515-07:00How to install database drivers for Tableau in Mac<span style="font-family: arial;">In a single day, I am asked twice the same question: how to install database drivers for Tableau in Mac? The question of the day is regarding the drivers for Presto and PostgreSQL databases. </span><span style="font-family: arial;">The docs online may not answer the question exactly. </span><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Here are the exact steps:</span></div><div><ol style="text-align: left;"><li><span style="font-family: arial;">Search the driver in question and download it to your computer. It's usually in the form of a .jar file.</span></li><li><span style="font-family: arial;">Open your Mac Finder and copy the .jar file.</span></li><li><span style="font-family: arial;">Hold the Option key and click the Finder menu Go. Select Library.</span></li><li><span style="font-family: arial;">Go to Library>Tableau>Drivers</span></li><li><span style="font-family: arial;">Paste the .jar file there.</span></li></ol></div><div><span style="font-family: arial;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3YE45DQ-4sB9W6SOhhN5aareBJfkLjcl6UdTtETY_GA-X_6-LbNTjpezqbcXq23ub76GaVA5If_3lmfcNKfaCBXz60XqmE2mOm9QY_AON2p2fHqZvQyR8pYtfbTMZRSGQF8aHAUtmhz8O8-GuSH3f8ipf7F2N5OOEuS_d6drCMBbBgGtPXmuY-Bev8g/s454/Screen%20Shot%202022-08-30%20at%2011.30.29%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="434" data-original-width="454" height="306" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEi3YE45DQ-4sB9W6SOhhN5aareBJfkLjcl6UdTtETY_GA-X_6-LbNTjpezqbcXq23ub76GaVA5If_3lmfcNKfaCBXz60XqmE2mOm9QY_AON2p2fHqZvQyR8pYtfbTMZRSGQF8aHAUtmhz8O8-GuSH3f8ipf7F2N5OOEuS_d6drCMBbBgGtPXmuY-Bev8g/s320/Screen%20Shot%202022-08-30%20at%2011.30.29%20PM.png" width="320" /></a></div>Without holding the Option key, the menu Library will not show up.</span></div><div><span style="font-family: arial;"><br />Now open your Tableau desktop. You should be able to access the database in question.</span></div><div><span style="font-family: arial;"><br /></span></div><div><span style="font-family: arial;">Have fun with Tableau!</span></div><div><br /></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-23113503656685410952022-08-17T20:39:00.004-07:002022-08-29T00:42:32.836-07:00Drawing Line Charts without Axis Offset in Tableau<p style="text-align: left;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxk9899lEPx-zkJsmTyihDrzPyVXI-zGrAZNQ4USeJ4A_JAaEe3CXqsurmMS1j5OGBYncwrth1Eg473DOTNZZmlcNG9LDszu0vntwXe6glccRBF3a7z-5wzWivDUrsuLTtT3uAJS_jJBKTGdMc2ENmZJNFAxJjYriTzx4weSRKWU8dDjU8ZQqRpNwzDg/s716/Screen%20Shot%202022-08-17%20at%208.39.50%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="585" data-original-width="716" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhxk9899lEPx-zkJsmTyihDrzPyVXI-zGrAZNQ4USeJ4A_JAaEe3CXqsurmMS1j5OGBYncwrth1Eg473DOTNZZmlcNG9LDszu0vntwXe6glccRBF3a7z-5wzWivDUrsuLTtT3uAJS_jJBKTGdMc2ENmZJNFAxJjYriTzx4weSRKWU8dDjU8ZQqRpNwzDg/s16000/Screen%20Shot%202022-08-17%20at%208.39.50%20PM.png" /></a></div><p style="text-align: left;"><span style="font-size: large;">There are always more than one ways to skin a cat. In Tableau, there is always one more way to design the same chart. Mastering them will give us more options to satisfy the various requirements we may be asked for</span><span style="font-size: x-large;">.</span></p><p></p><p style="text-align: left;"><span style="font-size: large;">Line chart is one of the most basic ones. Yet we can draw them in more ways than we care of. But there are many intricacies that are interesting to master.</span></p><p style="text-align: left;"><span style="font-size: large;">In graphical design, every tiny bit of space counts because the canvas may be limited or because of alignment with other parts of the dashboard. In this post, we are going to show how to design line charts with minimum axis offset.</span></p><h2 style="text-align: left;"><span style="font-size: x-large;">Axis Offset with Date in Columns Shelf</span></h2><p style="text-align: left;"><span style="font-size: large;">This is the default way of creating a line chart. One can notice that there exists always a horizontal offset at the first data mark from the vertical axis. With continuous date, the offset is big. With discrete date, there is half a tick offset. We have no way to edit the offset. </span></p><p style="text-align: left;"><span style="font-size: large;">Note that we dual axis'ed the line chart with an area chart to emphasize both the trend and the volume.</span></p><p style="text-align: left;"></p><div class="separator" style="clear: both; text-align: left;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEZ40MmFMwIHoDU87jkGj6axMFUij7walluuEhzMkrfmrLwwVD0jVWVS7omsmebKEtOtiywQGEr3kpr9hEnWaKsrTN_wv6pFAwAbzaLCQMQH9hFTZ4Presr2pje5WEyEEpsFhPZ5dEYbyO6fn7xo7Ndgy8FdyFH1G5xlrsnDt3RZfN218R4xcP5uKPGQ/s650/Screen%20Shot%202022-08-17%20at%204.41.47%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="360" data-original-width="650" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhEZ40MmFMwIHoDU87jkGj6axMFUij7walluuEhzMkrfmrLwwVD0jVWVS7omsmebKEtOtiywQGEr3kpr9hEnWaKsrTN_wv6pFAwAbzaLCQMQH9hFTZ4Presr2pje5WEyEEpsFhPZ5dEYbyO6fn7xo7Ndgy8FdyFH1G5xlrsnDt3RZfN218R4xcP5uKPGQ/s16000/Screen%20Shot%202022-08-17%20at%204.41.47%20PM.png" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5Eu2ULmrpahzIs15Mtl9fVSBzaLio_KbdXYZIoKnkJoIM8SeLW1XWmjieOeW14VYYhfkFUSKRdvE5jhzuh57HR3kBSk5i77izM798_BS_-aAoYKL6o6oPmcKeAaxVpUlmXKsthSgf-IyKk-cvEFTyITwrEro4U2BYedKtQOOACPTth0EoSOzNNgP7zg/s650/Screen%20Shot%202022-08-17%20at%204.42.45%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="360" data-original-width="650" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh5Eu2ULmrpahzIs15Mtl9fVSBzaLio_KbdXYZIoKnkJoIM8SeLW1XWmjieOeW14VYYhfkFUSKRdvE5jhzuh57HR3kBSk5i77izM798_BS_-aAoYKL6o6oPmcKeAaxVpUlmXKsthSgf-IyKk-cvEFTyITwrEro4U2BYedKtQOOACPTth0EoSOzNNgP7zg/s16000/Screen%20Shot%202022-08-17%20at%204.42.45%20PM.png" /></a></div></div><p></p><p style="text-align: left;"><span style="font-size: large;">Note that with discrete date, we need to turn on "<b>Show missing values</b>" to create the correct line chart.</span></p><h2 style="text-align: left;"><span style="font-size: x-large;">Axis Offset with INDEX() in Columns Shelf</span></h2><p style="text-align: left;"><span style="font-size: large;">To have more control over the axis offset, we can place continuous <b>INDEX</b>() in the <b>Columns</b> shelf and the date dimension in the <b>Detail</b> shelf. Set <b>INDEX</b>() to compute along the date dimension.</span></p><p style="text-align: left;"><span style="font-size: large;">An important caveat is we need to first set up the date dimension on <b>Rows</b>/<b>Columns</b> and turn on "<b>Show missing values</b>". Then drag it to the <b>Detail</b> shelf. This guarantees correct indexing even though we don't have contiguous data.</span></p><p style="text-align: left;"><span style="font-size: large;">Edit the axis to be fixed start at 1, and end with automatic.</span></p><p style="text-align: left;"></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTlxtc5zmAdLlPPxub2jdPyd8z3OTmn-w0Xzet2M8RSTMuKMBaRgEvjnzEauUgd1Veo16OtGEByxQnK65G0beSTKlA4M2Nm_AozkTH3fnYs7l84jDzps7vygI_9eT-ep579Mr9aXxWw0gUvIqRoanEhXsqdvHOhUK2umTqn7PusJrj3EOOBWTuaQ7Gqg/s442/Screen%20Shot%202022-08-16%20at%205.00.09%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="287" data-original-width="442" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhTlxtc5zmAdLlPPxub2jdPyd8z3OTmn-w0Xzet2M8RSTMuKMBaRgEvjnzEauUgd1Veo16OtGEByxQnK65G0beSTKlA4M2Nm_AozkTH3fnYs7l84jDzps7vygI_9eT-ep579Mr9aXxWw0gUvIqRoanEhXsqdvHOhUK2umTqn7PusJrj3EOOBWTuaQ7Gqg/s16000/Screen%20Shot%202022-08-16%20at%205.00.09%20PM.png" /></a></div><span style="font-size: large;">Now we have the line start at the vertical axis. The offset is 0 which is the least.</span><div style="text-align: left;"><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbu9jFSE1-sJ2trgXx4qOMONUs9GaxNngU7k_7I6tQflQdnoJGRiP4TLdemb_cpW-stLMHDVZQYQcjJD6hC7G7yF9xwqe-nIC_1q7GTe007ca2YGlkTZ67qMyoJtrg4Pud_N7lPutECD5lF4bGf5NVtPb6HYHkJnPEOlD4oJb9jq8Nuvus8xrJwoMgSg/s650/Screen%20Shot%202022-08-17%20at%206.07.52%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="360" data-original-width="650" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgbu9jFSE1-sJ2trgXx4qOMONUs9GaxNngU7k_7I6tQflQdnoJGRiP4TLdemb_cpW-stLMHDVZQYQcjJD6hC7G7yF9xwqe-nIC_1q7GTe007ca2YGlkTZ67qMyoJtrg4Pud_N7lPutECD5lF4bGf5NVtPb6HYHkJnPEOlD4oJb9jq8Nuvus8xrJwoMgSg/s16000/Screen%20Shot%202022-08-17%20at%206.07.52%20PM.png" /></a></div></div><div style="clear: both; text-align: left;"><span style="font-size: large;">We can still have <b>INDEX</b>() in discrete. The offset is the same as the chart with discrete date in Columns.</span></div><p></p><p style="text-align: left;"></p><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEil1T_07b57fmiSx5_OHXylJRgo3kcF7haIaocKm8kWFOwmlEttbnedkfbGNaiIQIBRaSZnQUyeWoD3DxTJXfXxdFvH8bnaVvjuWNcW_SQ8ow2yruUUaxu7UG9EnmIXSimK-4KXT3GppmtSywaBzaQUIJgjOHpwOP6duyOgMKpd_-4Q0a_kfzjtw8zB8w/s650/Screen%20Shot%202022-08-17%20at%206.06.46%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="360" data-original-width="650" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEil1T_07b57fmiSx5_OHXylJRgo3kcF7haIaocKm8kWFOwmlEttbnedkfbGNaiIQIBRaSZnQUyeWoD3DxTJXfXxdFvH8bnaVvjuWNcW_SQ8ow2yruUUaxu7UG9EnmIXSimK-4KXT3GppmtSywaBzaQUIJgjOHpwOP6duyOgMKpd_-4Q0a_kfzjtw8zB8w/s16000/Screen%20Shot%202022-08-17%20at%206.06.46%20PM.png" /></a></div></div><h3 style="clear: both; text-align: justify;"><span style="text-align: left;"><span style="font-size: x-large;">Line Chart Multiples with INDEX()</span></span></h3><p style="clear: both; text-align: justify;"><span style="font-size: large;"><span style="text-align: left;">We may need to create line chart multiples when splitting a line chart per category (State in the following example). We still like to create them with continuous <b>INDEX</b>() in the <b>Columns</b> shelf so that we have zero axis offset. Then we need to set up the date dimension with "Show missing values" in the <b>Columns</b> shelf before moving it to the <b>Detail</b> shelf.</span></span></p><p style="clear: both; text-align: justify;"><span style="font-size: large;"><span style="text-align: left;">A very important addition here is to place an <b>INDEX</b>() in the <b>Detail</b> shelf. Then set up its table calculations along <b>the date dimension</b> first and <b>State</b> second (Respect the order pls). </span></span></p><p style="clear: both; text-align: justify;"><span style="font-size: large;"><span style="text-align: left;">The 2-dimensional <b>INDEX</b>() is part of the data densification technique. We may not have all the data points at all dates and all states. This <b>INDEX</b>() in the <b>Detail</b> shelf will create a 2-d grid by <b>Date</b> and <b>State</b>. This will guarantee that each line chart per state will be indexed correctly by <b>INDEX</b>() in the <b>Columns</b> shelf. <br /></span><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU8jman7S8rEHqEXBBAyl1_6wHeDBb9cIl-STKa9wkf8IQxhmZ5O1fsCotwG2lhdS8pK4ZYTsSjjgQZaKsPrzCUbdWteFz9JbEkSqF_XEk5Mhk_MlS8KpB1SseT_8j4_zP752B03e9caUEr3QKjo0enfhKmNv6nsX7o290Ur1Rwd4dxfXgzDgh4X3Lsw/s747/Screen%20Shot%202022-08-17%20at%207.57.10%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="747" data-original-width="650" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU8jman7S8rEHqEXBBAyl1_6wHeDBb9cIl-STKa9wkf8IQxhmZ5O1fsCotwG2lhdS8pK4ZYTsSjjgQZaKsPrzCUbdWteFz9JbEkSqF_XEk5Mhk_MlS8KpB1SseT_8j4_zP752B03e9caUEr3QKjo0enfhKmNv6nsX7o290Ur1Rwd4dxfXgzDgh4X3Lsw/s16000/Screen%20Shot%202022-08-17%20at%207.57.10%20PM.png" /><br /></a><span style="text-align: left;">After the splitting by state, it is more likely that each line will miss some data at some dates.</span></span></p><p style="clear: both; text-align: justify;"><span style="font-size: large;"><span style="text-align: left;">Without the two dimensional <b>INDEX</b>(), the line chart multiples will look like this. All lines will start at <b>INDEX</b>()=1. This is not correct.<br /></span><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQQQAoEM9RWzyZDT3PDzVgFXQhc_VhAmL82PQkJFjpLnphCCfTe28Q2SbJdzZ4bJ_rPFDDwQ7Y1YH9Or56eDmgwZxCHMFDwuiYCHxo0jYSC-agALa6L9N8pBnbt4t25PocL0XmSUov1F7LX72kc1cE4iyPkxwitZIXl6gYPUgUxHbp_1t1LQbehRBXEA/s650/Screen%20Shot%202022-08-17%20at%208.05.27%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="516" data-original-width="650" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhQQQAoEM9RWzyZDT3PDzVgFXQhc_VhAmL82PQkJFjpLnphCCfTe28Q2SbJdzZ4bJ_rPFDDwQ7Y1YH9Or56eDmgwZxCHMFDwuiYCHxo0jYSC-agALa6L9N8pBnbt4t25PocL0XmSUov1F7LX72kc1cE4iyPkxwitZIXl6gYPUgUxHbp_1t1LQbehRBXEA/s16000/Screen%20Shot%202022-08-17%20at%208.05.27%20PM.png" /><br /></a></span></p><h2><span style="font-size: x-large;">Summary</span></h2><p style="text-align: left;"><span style="font-size: large;">In case a viz design requires that the line chart start with zero axis offset, we showed above that we can do it with <b>Continuous Index</b> in the <b>Columns</b> shelf. And in case we need to create line chart multiples with zero axis offset, we can add a two-dimensional <b>INDEX</b>() to achieve it.</span></p><p></p><p style="clear: both; text-align: justify;"><span style="font-size: large;">The demo workbook can be downloaded from <a href="https://public.tableau.com/views/ComparingAxisOffsetOptionsinLineChartDesign/Dashboard4?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">here</a>.</span><span style="font-size: x-large; text-align: left;"><br /></span></p><div class="separator" style="clear: both; text-align: justify;"><span style="font-size: x-large; text-align: left;"><br /></span></div><p></p></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-38860567469717491412022-08-04T23:42:00.008-07:002022-08-05T12:04:12.549-07:00Charting "Top N and Others" via Table Calculations in Tableau<p><span style="font-size: large;">I almost named the post as <i>Charting "Top N and Others" via Post-filtering</i>. Read on to understand why.</span></p><p><span style="font-size: large;">Visualizing "Top N and Others" is an often required business use case. A popular solution is by creating a top N set. That's the one I have been using through the years.</span></p><p><span style="font-size: large;">I wrote <a href="https://vizdiff.blogspot.com/2014/08/dynamic-pie-chart-la-liga-vs-premier.html" target="_blank">a post</a> in the context of a pie chart back in 2014 on grouping those smaller slices into 'Others'. </span></p><p><span style="font-size: large;">Recently, I encountered an issue at work. The dashboard won't let me using set. The problem with this set-based solution is that "set" is a filter <a href="https://vizdiff.blogspot.com/2015/12/the-order-of-operations-diagram-update.html" target="_blank">operating before the regular dimension filters</a>. We may want the dimension filters to execute before the set. This can be done by adding the dimension filters to context. In a multi-sheet dashboard, this may impact calculations on other sheets when a cross-sheet filter is applied, where the dimension filters may not be allowed to be in context.</span></p><p><span style="font-size: large;">There are <a href="https://vizdiff.blogspot.com/2020/02/20-calculation-use-cases-lod-vs-non-lod.html" target="_blank">almost always two ways</a> to skin a cat in Tableau: Pre-filtering and post-filtering.</span></p><div style="text-align: left;"><span style="font-size: large;"><b>Pre-filtering</b>: Filter the data set and keep only the data we need. This works </span><span style="font-size: large;"><span>with dimension filters, set filters, context filters etc.</span></span></div><div style="text-align: left;"><span style="font-size: large;"><span><br /></span><span><b>Post-filtering</b>: Filter the calculation results and keep the results we need. This works with table calculation filters.</span></span></div><div style="text-align: left;"><span style="font-size: large;"><span><br /></span><span>Here we are going to show a table calculations approach to solve the problem. Table calculations filtering takes place in the last stage in <a href="https://vizdiff.blogspot.com/2015/12/the-order-of-operations-diagram-update.html" target="_blank">Tableau's order of operations</a>. So it works after the regular dimension filters. This is a post-filtering approach.</span></span></div><p style="text-align: left;"><span style="font-size: large;">We are going to show our approach using the superstore data set and chart sales in top N states plus all others in one bar.</span></p><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdhua5_vZyK6q37LAJhggcHpP80VtY4DPBBnE0Plq9L8ZTrg52Pf4Rp23g5YXlJLcSI1p0TYif5FGx4kCa7E9cs1pSfvcZGaLcsbrQlxnvdyJiD9V4P7nKDu-LwbMe2QosIGXShSiqXNoBrQeZJCrKMy17ZvYp4oRYQ_iteOT8qJL-ABSLphVjtX1yOA/s799/Dashboard%201%20(1).png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="799" data-original-width="699" height="640" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgdhua5_vZyK6q37LAJhggcHpP80VtY4DPBBnE0Plq9L8ZTrg52Pf4Rp23g5YXlJLcSI1p0TYif5FGx4kCa7E9cs1pSfvcZGaLcsbrQlxnvdyJiD9V4P7nKDu-LwbMe2QosIGXShSiqXNoBrQeZJCrKMy17ZvYp4oRYQ_iteOT8qJL-ABSLphVjtX1yOA/w560-h640/Dashboard%201%20(1).png" width="560" /></a></div><p></p><div><span style="font-size: large;"><span><span>First let's create an integer <span style="color: #800180;">Top N</span> parameter. And have the <span style="color: #ffa400;">State</span> dimension is sorted by <span style="color: #3d85c6;">SUM</span>(<span style="color: #ffa400;">Sales</span>).</span></span></span></div><div><span style="font-size: large;"><span><span><br /></span></span><span><span>Then create a </span><span>formula for <span style="color: #ffa400;">Label Grouped</span> which includes the top N states plus "All Others".<br /></span></span><span><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkV9SypOtaigN6A7Xjxuw-72l1CV9uksuXHTg-TW_NS58FtxobR0W-HJ4XjpwWDlURkFK9S3lRmoJnzP32tDBF3CkbjTKOPLZkGQ6HsQLbsHFMM_Q2fpiyMUhWUAHI_Yw9tbZ-t1yDNfI9miZYs9CYacCGBuG-nr0pFd9DiNYgSyJKYrcZU3_i_ZLTfQ/s344/Screen%20Shot%202022-08-04%20at%2011.16.57%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="156" data-original-width="344" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhkV9SypOtaigN6A7Xjxuw-72l1CV9uksuXHTg-TW_NS58FtxobR0W-HJ4XjpwWDlURkFK9S3lRmoJnzP32tDBF3CkbjTKOPLZkGQ6HsQLbsHFMM_Q2fpiyMUhWUAHI_Yw9tbZ-t1yDNfI9miZYs9CYacCGBuG-nr0pFd9DiNYgSyJKYrcZU3_i_ZLTfQ/s16000/Screen%20Shot%202022-08-04%20at%2011.16.57%20PM.png" /><br /></a></span><span>Third, create a formula for <span style="color: #ffa400;">Sales Grouped</span> where all sales beyond top N states are grouped in a single quantity.<br /></span><span><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQJrwoiDMNyC8pGPjf0BBkgwPsvb7vil23YejcXK0-vwHwquMsuh0A7SPhLeuCWayWqPw47nPQQCMkRmeZ9tilYwmTB7-z3IG-8oYmio6zr634vJe9XKVcf0tMB8uWdPhDJTNjCIRekmZ8sXNjAEsebcV96TrvrIUZuWXCkSfN_BUrY_JzxqRUY-HXHg/s481/Screen%20Shot%202022-08-04%20at%2011.16.22%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="154" data-original-width="481" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiQJrwoiDMNyC8pGPjf0BBkgwPsvb7vil23YejcXK0-vwHwquMsuh0A7SPhLeuCWayWqPw47nPQQCMkRmeZ9tilYwmTB7-z3IG-8oYmio6zr634vJe9XKVcf0tMB8uWdPhDJTNjCIRekmZ8sXNjAEsebcV96TrvrIUZuWXCkSfN_BUrY_JzxqRUY-HXHg/s16000/Screen%20Shot%202022-08-04%20at%2011.16.22%20PM.png" /><br /></a></span><span><span style="text-align: left;">Last, create a top N+1 index filter for keeping the top N+1 bars.<br /></span></span><span><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0axpP5988TP95KFz2KNeIaWNV2-Y48ci68EgDuK-iolf7pOALcJ_INCwGAK2bviSWDj-QhlQGbId7sGpeMuX9rofr0M6t2uWhNw1Alqo1546VIWzXaNmqPrkbqHXNaHIYTYxTqwJZQhBC4fc3VxrGC5rpFvY2tanUIuf4WCuWLfZEW0nSE2-uZPr7EQ/s449/Screen%20Shot%202022-08-04%20at%2011.19.36%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="120" data-original-width="449" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg0axpP5988TP95KFz2KNeIaWNV2-Y48ci68EgDuK-iolf7pOALcJ_INCwGAK2bviSWDj-QhlQGbId7sGpeMuX9rofr0M6t2uWhNw1Alqo1546VIWzXaNmqPrkbqHXNaHIYTYxTqwJZQhBC4fc3VxrGC5rpFvY2tanUIuf4WCuWLfZEW0nSE2-uZPr7EQ/s16000/Screen%20Shot%202022-08-04%20at%2011.19.36%20PM.png" /><br /></a></span><span><span style="text-align: left;">The resulting chart looks like this:</span></span><br /></span></div><div class="separator" style="clear: both; text-align: center;"><span style="font-size: large; margin-left: 1em; margin-right: 1em;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiB9MZ68bTFhHvSSuaayMB31pUXbdRuToZAPaSkgGK-B3jxYM11f-Cx2jUj9ndB2b7tBvQXaf3L-UoUBxVVVFVrOmg6y9o9iY2gayvuHsFTtbSBMQUZn0xc6JaShqL_yS_d85V3I7mokzbJ4pEzijUPPcfAd56_uQMVbUz76AA0O6tsoBtsXP5U7Xwr_g/s922/Screen%20Shot%202022-08-04%20at%2011.37.18%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="840" data-original-width="922" height="584" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiB9MZ68bTFhHvSSuaayMB31pUXbdRuToZAPaSkgGK-B3jxYM11f-Cx2jUj9ndB2b7tBvQXaf3L-UoUBxVVVFVrOmg6y9o9iY2gayvuHsFTtbSBMQUZn0xc6JaShqL_yS_d85V3I7mokzbJ4pEzijUPPcfAd56_uQMVbUz76AA0O6tsoBtsXP5U7Xwr_g/w640-h584/Screen%20Shot%202022-08-04%20at%2011.37.18%20PM.png" width="640" /></a></span></div><div class="separator" style="clear: both; text-align: center;"><div style="text-align: justify;"><span style="font-size: large;"><span style="text-align: left;">Make sure that all the table calculations are computed along </span><span style="color: #ffa400; text-align: left;">State</span><span style="text-align: left;">.</span></span></div><div style="text-align: justify;"><span style="text-align: left;"><span style="font-size: large;"><br /></span></span></div><span style="font-size: large; text-align: left;"><div style="text-align: justify;">Voila, the demo workbook can be downloaded from <a href="https://public.tableau.com/views/TopNOthers_16596800154930/Dashboard1?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">here</a>.</div></span></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-85178237660194142962022-07-15T00:25:00.002-07:002023-09-14T01:23:06.537-07:00Calculating YoY of Quarterly and Yearly Sums in Tableau<p>Angel works in Finance. She often asks me questions on calculations in a table. Today I got this question: How to calculate Year over Year (YoY) change ratios for both quarterly and yearly sums, in a single sheet?</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrT4QHwpBpPM8J7jOmWZarOBM537-H04mias52FxG28vbj_huBe5kfCt8M6KD7LjzHJ9opSvn-tvoxkKyV8rV53OMjsWXDzDbMEbW643FkkDOgInoKRjYDy5WfYRQGM0AzOYdXDMSc3VAm-VMHjnV6jAwUKJ4ykZXXMOYU1lVNWAgbwink4AFWKrX9gg/s622/Screen%20Shot%202022-07-14%20at%2011.55.23%20PM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="141" data-original-width="622" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhrT4QHwpBpPM8J7jOmWZarOBM537-H04mias52FxG28vbj_huBe5kfCt8M6KD7LjzHJ9opSvn-tvoxkKyV8rV53OMjsWXDzDbMEbW643FkkDOgInoKRjYDy5WfYRQGM0AzOYdXDMSc3VAm-VMHjnV6jAwUKJ4ykZXXMOYU1lVNWAgbwink4AFWKrX9gg/s16000/Screen%20Shot%202022-07-14%20at%2011.55.23%20PM.png" /></a></div><div>Here is the solution we got. First there are two parts for the YoY calculation.</div><div><br /></div>For the quarterly YoY, we can apply this formula:<div><span style="color: #3d85c6;">SUM</span>([<span style="color: #ffa400;">Sales</span>])/<span style="color: #3d85c6;">LOOKUP</span>(<span style="caret-color: rgb(61, 133, 198); color: #3d85c6;">SUM</span>([<span style="caret-color: rgb(255, 164, 0); color: #ffa400;">Sales</span>]), -4) -1</div><div><br /></div><div>For the yearly YoY, the above formula doesn't work. We need a slightly different one like:</div><div><span style="caret-color: rgb(61, 133, 198); color: #3d85c6;">SUM</span>([<span style="caret-color: rgb(255, 164, 0); color: #ffa400;">Sales</span>])/<span style="caret-color: rgb(61, 133, 198); color: #3d85c6;">LOOKUP</span>(<span style="caret-color: rgb(61, 133, 198); color: #3d85c6;">SUM</span>([<span style="caret-color: rgb(255, 164, 0); color: #ffa400;">Sales</span>]), -1) -1</div><div><br /></div><div>To combine them in a single one, we got this:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU8VpjfyQy3sbBnA5ISWVKfZ7SWxTb5qKjRl2eGZPLhLIRtduHpffohgFKAS5ykMffFi3dSD2glNKX0vua3AeGPaK_FLoqQXFXs2yELtMp9WqFKlQICdrRGF4bh4pEYqTuHucbOsYB6pQ6uHXMeVUMEcLWjPJCWFYFNKcpNCL69HXJno5DBco-Kj6GwQ/s383/Screen%20Shot%202022-07-15%20at%2012.00.15%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="146" data-original-width="383" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEjU8VpjfyQy3sbBnA5ISWVKfZ7SWxTb5qKjRl2eGZPLhLIRtduHpffohgFKAS5ykMffFi3dSD2glNKX0vua3AeGPaK_FLoqQXFXs2yELtMp9WqFKlQICdrRGF4bh4pEYqTuHucbOsYB6pQ6uHXMeVUMEcLWjPJCWFYFNKcpNCL69HXJno5DBco-Kj6GwQ/s16000/Screen%20Shot%202022-07-15%20at%2012.00.15%20AM.png" /></a></div><div>Note that <span style="color: #3d85c6;">CountD</span>()=1 is equivalent of <span style="color: #3d85c6;">Max</span>()=<span style="color: #3d85c6;">Min</span>() when we deal with grand total/subtotal calculations.</div><div><br /></div>Then the results are shown in this table:<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4B5IDjNJZxkS8jl0fj3mD72x3ZR3xAByWLKFOszcotz96oYEV2mPoqBW1QwQcU_-98KBaNk7SM7J3Z8DPcxgUUW0aCIfZnS8Fthuqr82r3autExV1r_ElZLtwbkzyp1ZcS82cOH5Yi0GSjHatpiM2N05P7TcaOjxNGTEPIDZchi24STWndG0V32kj7w/s658/Screen%20Shot%202022-07-15%20at%2012.03.31%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="148" data-original-width="658" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj4B5IDjNJZxkS8jl0fj3mD72x3ZR3xAByWLKFOszcotz96oYEV2mPoqBW1QwQcU_-98KBaNk7SM7J3Z8DPcxgUUW0aCIfZnS8Fthuqr82r3autExV1r_ElZLtwbkzyp1ZcS82cOH5Yi0GSjHatpiM2N05P7TcaOjxNGTEPIDZchi24STWndG0V32kj7w/s16000/Screen%20Shot%202022-07-15%20at%2012.03.31%20AM.png" /></a></div><div>The above formula works well logically. Here we have a few variants that look simpler.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8qf34ludN9H-SX1rVO5Pi2u--XjmMwkCN7zkowr7VbZsgsO8Ro4VtiXrPZ7Hc52HCblTtN9A_5Xz2zO67AYxqs-fu3waWSXTWJu7-kDWfHqyDy-TmMQE_d7M21AgF16r4jF52WC4TMn63Mm0fcaTGbTe7556YUV0MCztzvZz2IpwI8r25jtzL5YDWZQ/s305/Screen%20Shot%202022-07-15%20at%2012.12.53%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="166" data-original-width="305" height="166" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEh8qf34ludN9H-SX1rVO5Pi2u--XjmMwkCN7zkowr7VbZsgsO8Ro4VtiXrPZ7Hc52HCblTtN9A_5Xz2zO67AYxqs-fu3waWSXTWJu7-kDWfHqyDy-TmMQE_d7M21AgF16r4jF52WC4TMn63Mm0fcaTGbTe7556YUV0MCztzvZz2IpwI8r25jtzL5YDWZQ/s1600/Screen%20Shot%202022-07-15%20at%2012.12.53%20AM.png" width="305" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6qQb84dNn5goxNulInD3b1Pe96EMD2K_ZyVRviqzn3AG3QozxMrRUXb2JHLgIhaWrc_iD25pjxwLSj9WogUKWC7R_mgLeyaIYS_eibvP6_aLW62h5ThOfiAN5joX_5KGSlMF2f5k24Qa1fm0Td_K9w_VvfT9EmQcIVFU1IaNWjx7xL1_-fUjtC8Q9sQ/s297/Screen%20Shot%202022-07-15%20at%2012.13.21%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="165" data-original-width="297" height="165" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEg6qQb84dNn5goxNulInD3b1Pe96EMD2K_ZyVRviqzn3AG3QozxMrRUXb2JHLgIhaWrc_iD25pjxwLSj9WogUKWC7R_mgLeyaIYS_eibvP6_aLW62h5ThOfiAN5joX_5KGSlMF2f5k24Qa1fm0Td_K9w_VvfT9EmQcIVFU1IaNWjx7xL1_-fUjtC8Q9sQ/s1600/Screen%20Shot%202022-07-15%20at%2012.13.21%20AM.png" width="297" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj241scp1K0XUKJVEDta0eBxsqJmTMKiSuymS3Wyh-elvWtPa7lH2q7d1uiKHJfZFCfahJ_nYKCT_moinAiPu2ciAkJaKvOKxHz1b0pgTCb00zzcQmQfl7ri7wK8gCroxrrWc28HudRmHOPm7hjcmu-Mg9iJwBd393SVPyEEdIzl9QbU7fm4qYbVumTeQ/s525/Screen%20Shot%202022-07-15%20at%2012.13.57%20AM.png" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="132" data-original-width="525" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEj241scp1K0XUKJVEDta0eBxsqJmTMKiSuymS3Wyh-elvWtPa7lH2q7d1uiKHJfZFCfahJ_nYKCT_moinAiPu2ciAkJaKvOKxHz1b0pgTCb00zzcQmQfl7ri7wK8gCroxrrWc28HudRmHOPm7hjcmu-Mg9iJwBd393SVPyEEdIzl9QbU7fm4qYbVumTeQ/s16000/Screen%20Shot%202022-07-15%20at%2012.13.57%20AM.png" /></a></div>They all work for YoY calculations. Pick the one you like if you need one.<div><br /></div><div>Download the demo workbook <a href="https://public.tableau.com/views/QuarterlyYearlyYoY/Dashboard1?:language=en-US&publish=yes&:display_count=n&:origin=viz_share_link" target="_blank">here</a>.</div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-25242599084227736372022-05-26T06:01:00.000-07:002022-05-26T06:01:02.236-07:00Imagining Tableau Social<p>Just came back from Tableau Conference 2022 at Las Vegas. What an exciting event! The most exciting thing is reuniting with old friends and meeting with the datafam people known online for years.</p><p>Attended first time the Tableau Visionary summit. It's a great opportunity for meeting other visionaries, Tableau/Salesforce PMs and executives.</p><p>During the summit, one executive (sorry I am terrible with names) challenged us with a question: how to increase the number of tableau users by 1 million?</p><p>This rang a bell within me. I guess I have the solution to that! Frankly speaking, I have been imagining a social media platform built around data vizzies for years. To me, data viz is a media type by itself, just like video, image, sound, and text. Even PowerPoint has its own social platform: <a href="https://SlideShare.net">SlideShare.net</a>. So, data viz deserves a social platform of its own. Let's call it <b>Tableau Social</b>.</p><h3 style="text-align: left;">What is Tableau Social?</h3><p>It's a social media platform where people share vizzing activities (publish, update), discover vizzies and interact with authors/viewers (like, share, comment).</p><h3 style="text-align: left;">Why Tableau Social?</h3><p>Currently Tableau communities are mostly active in Twitter, LinkedIn, Youtube, Blog sphere, Tableau Forum and Tableau Public. Each of them has somewhat different functionalities and utilities. Tableau Social would be the one place that aggregates the links to content scattering around in various platforms.</p><h3 style="text-align: left;">How does Tableau Social work?</h3><p>It works like your familiar timeline with a focus on vizzies. It's the Instagram for vizzies. Or shall we call it InstaViz.</p><p>One can follow his/her favorite vizzers!</p><p>One can like, comment any viz of interest.</p><p>One can share/forward any viz of interest within Tableau Social or to other social platforms.</p><h3 style="text-align: left;">Where to start?</h3><p>The version zero can be built upon Tableau Public: </p><p>- Add like and and comment functions to every viz.</p><p>- Add a timeline user interface in addition to the current tiled one.</p><h3 style="text-align: left;">Build a Magnet</h3><p>I believe, with Tableau Social/InstaViz, we can achieve multi million tableau users faster than any other type of marketing campaign. And it's a sustainable platform for a long time to come. More importantly, it's a magnet that will attract those who are interested in the data visualization revolution.</p><p><br /></p><p><br /></p>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-58230794954843935322022-04-01T15:25:00.011-07:002022-05-18T08:22:25.903-07:00Imagining an Enhanced Formula Editor for Tableau<div style="text-align: left;">A little enhancement in the formula editor can make a big difference for whose who create formula all the time in Tableau. Here are my wishes for a future editor. </div><h2 style="text-align: left;"><span style="font-family: inherit; font-size: medium;"><span><span face="Merriweather-Regular, serif" style="caret-color: rgb(51, 51, 51); color: #333333;">Highlighting Syntax Words</span></span></span></h2><div style="text-align: left;"><span style="font-family: inherit;"><span face="Merriweather-Regular, serif" style="caret-color: rgb(51, 51, 51); color: #333333; font-size: 1rem;">Currently a formula in Tableau can look plain and a bit uninspiring.</span></span></div><p style="box-sizing: border-box; caret-color: rgb(51, 51, 51); color: #333333; font-size: 1rem; line-height: 1.8; margin: 0px; padding: 0px;"><span style="font-family: inherit;"><span> </span><img class="image-1 jive-image" height="125" src="https://community.tableau.com/servlet/rtaImage?eid=0874T000000HAs1&feoid=Body&refid=0EM4T000000U1pE" style="border: 0px; box-sizing: border-box; height: auto; margin-bottom: var(--lwc-spacingSmall,0.75rem); max-width: 100%; vertical-align: middle;" width="236" /></span></p><div style="text-align: left;"><span style="font-family: inherit;">I would suggest highlighting the syntax words like <span style="box-sizing: border-box; color: #2873ee;">Case, When, Then, End</span> etc. <span style="font-size: 1rem;">The result should look like this</span></span></div><p style="box-sizing: border-box; caret-color: rgb(51, 51, 51); color: #333333; font-size: 1rem; line-height: 1.8; margin: 0px; padding: 0px;"><span style="font-family: inherit;"><span> </span><img class="image-2 jive-image" height="144" src="https://community.tableau.com/servlet/rtaImage?eid=0874T000000HAs1&feoid=Body&refid=0EM4T000000U1pF" style="border: 0px; box-sizing: border-box; height: auto; margin-bottom: var(--lwc-spacingSmall,0.75rem); max-width: 100%; vertical-align: middle;" width="194" /></span></p><div style="text-align: left;"><span style="font-family: inherit;">Talking about Tableau being a visualization leader ... This will distinguish user-input texts from Tableau-defined syntactic words. It will make the formula easier to read and debug.</span></div><p style="box-sizing: border-box; caret-color: rgb(51, 51, 51); color: #333333; font-size: 1rem; line-height: 1.8; margin: 0px; padding: 0px;"></p><div style="text-align: left;"><span style="font-family: inherit;"><br /></span></div><div style="text-align: left;"><span style="font-family: inherit;">I would also add that we could highlight the quotes so that it becomes</span></div><div style="text-align: left;"><span style="font-family: inherit;"><br /></span></div><div style="text-align: left;"><span style="box-sizing: border-box; font-family: inherit;"><span style="box-sizing: border-box;"><span style="color: #3d85c6;"><span> </span>When</span></span><span style="box-sizing: border-box; color: #2873ee;"> <span style="box-sizing: border-box; color: #eb7a3d;">'</span></span><span style="color: #333333;">California</span><span style="box-sizing: border-box; color: #eb7a3d;"><span style="box-sizing: border-box;">'</span></span><span style="box-sizing: border-box; color: #2873ee;"> </span><span style="box-sizing: border-box;"><span style="color: #3d85c6;">Then</span></span><span style="box-sizing: border-box; color: #2873ee;"> <span style="box-sizing: border-box; color: #eb7a3d;"><span style="box-sizing: border-box;">'</span></span></span><span style="color: #333333;">CA</span><span style="box-sizing: border-box; color: #eb7a3d;"><span style="box-sizing: border-box;">'</span></span></span></div><div style="text-align: left;"><span style="box-sizing: border-box; font-family: inherit;"><span style="box-sizing: border-box; color: #eb7a3d;"><span style="box-sizing: border-box;"><br /></span></span></span></div><div style="text-align: left;"><span style="box-sizing: border-box; color: #303030; font-family: inherit;">When there are multiple opening brackets, we may not know how many closing brackets are needed. </span><span style="color: #303030; font-family: inherit;">So if we could color code the brackets, it may give users hints on how many closing brackets are missing and which is matching which. </span></div><div style="text-align: left;"><span style="color: #303030; font-family: inherit;"><br /></span></div><div style="text-align: left;"><span style="font-family: inherit;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="box-sizing: border-box;"><span style="color: #3d85c6;"><span> </span>WINDOW_MAX</span><span style="color: red;"><b>(</b></span></span></span></span><span style="box-sizing: border-box; color: #303030;">IF</span><span style="box-sizing: border-box; color: #303030;"> </span><span style="box-sizing: border-box;"><span style="color: #3d85c6;">ATTR</span><span style="box-sizing: border-box; color: #04ff00;"><b>(</b></span><span style="color: #ffa400;">Category</span><span style="box-sizing: border-box; color: #04ff00;"><b>)</b></span><span style="color: #303030;">="Technology" THEN </span><span style="color: #3d85c6;">SUM</span><span style="box-sizing: border-box; color: #04ff00;"><b>(</b></span><span style="color: #ffa400;">Sales</span><span style="box-sizing: border-box; color: #04ff00;"><b>)</b></span><span style="color: #303030;"> END</span><span style="box-sizing: border-box; color: red;"><b>)</b></span></span></span></div><h2><span style="font-family: inherit; font-size: medium;">Typing Suggestions</span></h2><div style="text-align: left;"><span style="font-family: inherit;">I also think of a new suggestive option: When a user types <span style="color: #3d85c6;">CASE</span>, Tableau suggests an option like</span></div><div class="p-rich_text_section"><pre class="c-mrkdwn__pre" data-stringify-type="pre" style="--saf-0: rgba(var(--sk_foreground_low,29,28,29),0.13); background: rgba(var(--sk_foreground_min,29,28,29),0.04); border-bottom-left-radius: 4px; border-bottom-right-radius: 4px; border-top-left-radius: 4px; border-top-right-radius: 4px; border: 1px solid var(--saf-0); box-sizing: inherit; counter-reset: list-0 0 list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; font-size: 12px; font-variant-ligatures: none; line-height: 1.50001; margin-bottom: 4px; margin-top: 4px; orphans: 2; overflow-wrap: break-word; padding: 8px; tab-size: 4; text-decoration-thickness: initial; white-space: pre-wrap; widows: 2; word-break: normal;"><span style="color: #3d85c6; font-family: inherit;">CASE
WHEN THEN
WHEN THEN
END</span></pre></div><div style="text-align: left;"><span style="font-family: inherit;">This goes beyond completing a <b>word</b>: completing a <b>statement </b>or a<b> function</b>. Same goes for other structured statements like</span></div><div class="p-rich_text_section"><span style="color: #3d85c6; font-family: inherit;"><pre class="c-mrkdwn__pre" data-stringify-type="pre" style="--saf-0: rgba(var(--sk_foreground_low,29,28,29),0.13); background: rgba(var(--sk_foreground_min,29,28,29),0.04); border-bottom-left-radius: 4px; border-bottom-right-radius: 4px; border-top-left-radius: 4px; border-top-right-radius: 4px; border: 1px solid var(--saf-0); box-sizing: inherit; counter-reset: list-0 0 list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; font-size: 12px; font-variant-ligatures: none; line-height: 1.50001; margin-bottom: 4px; margin-top: 4px; orphans: 2; overflow-wrap: break-word; padding: 8px; tab-size: 4; text-decoration-thickness: initial; white-space: pre-wrap; widows: 2; word-break: normal;">IF
THEN
ELSE
END</pre></span></div><div style="text-align: left;">And instead of DATEDIFF(), Tableau could suggest a bit more. The list can go on and on:</div><div style="text-align: left;"><div class="p-rich_text_section"><span style="color: #3d85c6; font-family: inherit;"><pre class="c-mrkdwn__pre" data-stringify-type="pre" style="--saf-0: rgba(var(--sk_foreground_low,29,28,29),0.13); background: rgba(var(--sk_foreground_min,29,28,29),0.04); border-bottom-left-radius: 4px; border-bottom-right-radius: 4px; border-top-left-radius: 4px; border-top-right-radius: 4px; border: 1px solid var(--saf-0); box-sizing: inherit; counter-reset: list-0 0 list-1 0 list-2 0 list-3 0 list-4 0 list-5 0 list-6 0 list-7 0 list-8 0 list-9 0; font-size: 12px; font-variant-ligatures: none; line-height: 1.50001; margin-bottom: 4px; margin-top: 4px; orphans: 2; overflow-wrap: break-word; padding: 8px; tab-size: 4; text-decoration-thickness: initial; white-space: pre-wrap; widows: 2; word-break: normal;">DATEDIFF('', , )
IIF( , , )
IFNULL( , )
LOOKUP( , )</pre></span></div></div><div style="text-align: left;"><span style="font-family: inherit;">Voila. Part of this is from an old post in 2016 on <a href="https://community.tableau.com/s/idea/0874T000000HAs1QAG/detail" target="_blank">Highlighting Syntax Words in Formula Editor</a>. Hope it will become real someday. The purpose is for the tool to help productivity, faster typing, less typos and easier read and debug.</span></div><div style="text-align: left;"><span style="font-family: inherit;"><br /></span></div><div style="text-align: left;"><span style="font-family: inherit;">Your inputs are also welcome. Feel free to leave comments below or contact me at <a href="https://twitter.com/aleksoft" style="color: #009eb8; display: inline; font-size: 14px; outline: none; text-align: justify; text-decoration: none;" target="_blank">twitter: @aleksoft</a><span style="font-size: 1rem;"> or <a href="https://www.linkedin.com/in/alexmou/" target="_blank">LinkedIn</a></span></span></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-19012129381412880732022-03-20T02:03:00.016-07:002022-04-01T09:58:54.046-07:00Filtering Partial Date Period in Tableau<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjbiqWOd6tpaxcHP_i0uShRwRTEaRA-Mx52EScS2694D2uvqS5SIhUdEzdJMrCLNqifqTvDRwgYURdXYrGvYw6kGRecLSTnDWVTTCzqT04DWAbDJyudAGya-B0jwq5YPskKQclwR14Dsz2920DJ5AKt7QXqYeb7GuQS0qJg9GMkgQFonWrveEsr-vEZAA=s985" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="787" data-original-width="985" height="512" src="https://blogger.googleusercontent.com/img/a/AVvXsEjbiqWOd6tpaxcHP_i0uShRwRTEaRA-Mx52EScS2694D2uvqS5SIhUdEzdJMrCLNqifqTvDRwgYURdXYrGvYw6kGRecLSTnDWVTTCzqT04DWAbDJyudAGya-B0jwq5YPskKQclwR14Dsz2920DJ5AKt7QXqYeb7GuQS0qJg9GMkgQFonWrveEsr-vEZAA=w640-h512" width="640" /></a></div><p>For the sake of uniformity in a bar chart, we may need to filter out dates in the latest partial week, month, quarter or year. That is what Parinita asked me about a filter to do just that.</p><p>How to design a filter for it? Especially, we need the design to take into consideration of dynamic date range which can be changed by the user.</p><p>Here we will show two approaches to it. They differentiate themselves by <a href="https://vizdiff.blogspot.com/2015/12/the-order-of-operations-diagram-update.html" target="_blank">the order of filtering</a>. Note that we will use a parameter <span style="color: #800180;">Date Grain</span> to denote the selected date period. A grain can be any of day, week, month, quarter and year. We will use the superstore data set to create examples.</p><h3 style="text-align: left;">1. LOD Approach</h3><p>This is a pre filter approach. It will remove the data rows in the partial period before calculations. With LOD calculations, <b>we must add the date range filter to the context</b>.</p><p>Here is the <span style="color: #ffa400;">Date by Grain</span>:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhIuMezegGqqWR5cKuoHVY8k15_6ho4LkdXW8Hht4ufxEHRpmFURlxYJ6r6qsMMvErQXroeUPFnMgC8kVXlr5aGuunuTS6p7TRyFdz2B0D7i7AMEwMP_hEeS5fEPOAutKff42IC8vLX-IzOmfPGsBWv_4Pqr-LYW9d9ukb-Po5wNdGCt_CgAmI8nsRLyQ=s363" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="87" data-original-width="363" src="https://blogger.googleusercontent.com/img/a/AVvXsEhIuMezegGqqWR5cKuoHVY8k15_6ho4LkdXW8Hht4ufxEHRpmFURlxYJ6r6qsMMvErQXroeUPFnMgC8kVXlr5aGuunuTS6p7TRyFdz2B0D7i7AMEwMP_hEeS5fEPOAutKff42IC8vLX-IzOmfPGsBWv_4Pqr-LYW9d9ukb-Po5wNdGCt_CgAmI8nsRLyQ=s16000" /></a></div><p>First we get the max date of the data set</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjCzkUulr-7dJk7hK7QaY1PEFSBI7ZPDHgTqptJVKD_cR_XFLBvMecxPr4aOj3J-wMKDBRn0ibjtCFe5JqFOpb8fBwD_HVfZUS1TYILHusaURz2Rh50wIUk34lhendx3h5O_fgPIeYI1hJfXqxUoQ1uo7C7EpusXMcz7Jv6TkkeL1T8jj7i9cOov79i7w=s224" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="86" data-original-width="224" height="86" src="https://blogger.googleusercontent.com/img/a/AVvXsEjCzkUulr-7dJk7hK7QaY1PEFSBI7ZPDHgTqptJVKD_cR_XFLBvMecxPr4aOj3J-wMKDBRn0ibjtCFe5JqFOpb8fBwD_HVfZUS1TYILHusaURz2Rh50wIUk34lhendx3h5O_fgPIeYI1hJfXqxUoQ1uo7C7EpusXMcz7Jv6TkkeL1T8jj7i9cOov79i7w" width="224" /></a></div><p>Then we have the filter as follows. Select true to remove the partial period.</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEi_bYEQ6-RVNl2G3NGb75a2IpVKXu7e3UyHoIYHE-W1PKjT8h8u9U8UBviDTHeSey03mcKt98937CASD5GSZpQ9GOKZRpPTJXiuKqhz8nCwXsAtaOHCpLEq8BkQnZ89hr__w32iMcSXGL5SStjcgTz8WQtcIW1ltw1Xqf6u8Z-u3A4x-Iy6rl48JUUfHQ=s359" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="123" data-original-width="359" src="https://blogger.googleusercontent.com/img/a/AVvXsEi_bYEQ6-RVNl2G3NGb75a2IpVKXu7e3UyHoIYHE-W1PKjT8h8u9U8UBviDTHeSey03mcKt98937CASD5GSZpQ9GOKZRpPTJXiuKqhz8nCwXsAtaOHCpLEq8BkQnZ89hr__w32iMcSXGL5SStjcgTz8WQtcIW1ltw1Xqf6u8Z-u3A4x-Iy6rl48JUUfHQ=s16000" /></a></div><h3 style="text-align: left;">2. Table Calc Approach</h3><p>This is a post filter approach. It will remove the calculation results belonging to the partial period. This post filter is always second to date range filter which doesn't have to be in context.</p><p>For <span style="color: #ffa400;">Date Grain</span>, we will need to use the aggregate form:</p><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg7hEfQbLJl-hCekT2LNqjgJ60XPqbEQS-vz_GVDq2w0sIpSkG6Ovli2wzczA7V0W-x0_024f_qxeLMBzClCJBVO2o-rmTcSIibxS3UI9VHtr4g0_EVx-P7JhYJkNuPtWKenJ_41buVhAjO0bA2fagV-YvO2LAxZIEPH0Bb3CDfEOnqoWygRDZzetPfMA=s402" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="87" data-original-width="402" src="https://blogger.googleusercontent.com/img/a/AVvXsEg7hEfQbLJl-hCekT2LNqjgJ60XPqbEQS-vz_GVDq2w0sIpSkG6Ovli2wzczA7V0W-x0_024f_qxeLMBzClCJBVO2o-rmTcSIibxS3UI9VHtr4g0_EVx-P7JhYJkNuPtWKenJ_41buVhAjO0bA2fagV-YvO2LAxZIEPH0Bb3CDfEOnqoWygRDZzetPfMA=s16000" /></a></div></div><p>Same as above, we need to first calculate the Max Date of the data set. </p><div class="separator" style="clear: both; text-align: center;"></div>Thus we use <span style="color: #3d85c6;">Window_Max</span>() to calculate it which is available everywhere. Note that we need to set it up to compute along <span style="color: #ffa400;">Date by Grain</span>.<div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuAzNhUUq_6jr2EKQ30-HujI0VLGn8w6bjvkc1KIC3fS6xPc5X5uGgvP7VqIDaiJVMsv16OMwqHiUsQR9Wa9t1yk8m7Wv7ncrEVVpO9uMxZ7F5P3lmcrODt5jkmGodQSP00l1kZO-vMATKArlcpgjW8pU2j01Fiy12WdBlxSbH3Sq3aSAJCYPbFhUw0g/s290/Screen%20Shot%202022-03-31%20at%2010.34.39%20PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="96" data-original-width="290" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEiuAzNhUUq_6jr2EKQ30-HujI0VLGn8w6bjvkc1KIC3fS6xPc5X5uGgvP7VqIDaiJVMsv16OMwqHiUsQR9Wa9t1yk8m7Wv7ncrEVVpO9uMxZ7F5P3lmcrODt5jkmGodQSP00l1kZO-vMATKArlcpgjW8pU2j01Fiy12WdBlxSbH3Sq3aSAJCYPbFhUw0g/s16000/Screen%20Shot%202022-03-31%20at%2010.34.39%20PM.png" /></a></div><div>The resulting filter is as follows:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsVxYMeeoKZ84gWZubCUuipMlBJ3CHszOiGZvX3RhH_h8H2eYWw5mXhBKMv1WVegvcGB0I_rvDs7vdTg-7yYXSFTNNsoVuJDd3qr3zD0icwQ6xAkLcayeY80zX4F6oa3Ob9OxZ5JWUrUDjb3B_iLRlC5qeU4C3uALHke-gwYcqUdGz2R2-SZpgEpOrZQ/s398/Screen%20Shot%202022-03-31%20at%2010.35.52%20PM.png" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="106" data-original-width="398" src="https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEhsVxYMeeoKZ84gWZubCUuipMlBJ3CHszOiGZvX3RhH_h8H2eYWw5mXhBKMv1WVegvcGB0I_rvDs7vdTg-7yYXSFTNNsoVuJDd3qr3zD0icwQ6xAkLcayeY80zX4F6oa3Ob9OxZ5JWUrUDjb3B_iLRlC5qeU4C3uALHke-gwYcqUdGz2R2-SZpgEpOrZQ/s16000/Screen%20Shot%202022-03-31%20at%2010.35.52%20PM.png" /></a></div><div><h3 style="text-align: left;">Postscript</h3><p>The LOD approach seems a little simpler. However we have to be very careful about the context. It may go wrong if we don't set the context filter correctly.</p><p>The Table Calc approach will work regardless of context. The table calculations require setting up along the date dimension carefully. </p><p>In most cases, there is a solution via LOD and also <a href="https://vizdiff.blogspot.com/2020/02/20-calculation-use-cases-lod-vs-non-lod.html" target="_blank">an equivalent solution via table calculations</a>. Pick the one that you feel most comfortable about.</p><p>A demo workbook can be downloaded <a href="https://public.tableau.com/app/profile/a.m.5517/viz/PartialDatePeriodFilterinTableau/Dashboard1?publish=yes" target="_blank">here</a>. </p><p>Feel free to leave comments below or contact me at <a href="https://twitter.com/aleksoft" target="_blank">twitter: @aleksoft</a></p></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-36477800051373716642022-02-27T01:09:00.020-08:002022-03-16T19:41:21.331-07:00How to Send Email from Tableau with a Template<p><span style="font-family: inherit;">Before Belinda asked me about <a href="https://vizdiff.blogspot.com/2022/02/how-to-make-phone-calls-from-tableau.html" target="_blank">making phone calls from Tableau dashboard</a>, she had some issues in creating an email template in Tableau. Many people might have known how to do the basics. Here I would share some tips on composing an email template with some formatting, and on making it easier to edit the template.</span></p>
<h3 style="text-align: left;"><span style="font-family: inherit; font-size: medium;">Email Template</span></h3><p>
<span style="font-family: inherit;">The email template is expected to be as follows, in which there are a few fields from the data table. Belinda will use the template to communicate with her business partners regularly.</span></p><div style="text-align: left;"><i><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhdkF5escpMwlTcXeUqEx7q6V7-klBgwwQrQTsssByWEMXBgTH0sSQSVPFmWixIfFaJB7wA2A6k9zBK8Sbks5f2Ax7aJhNWPooQ7jYVAr3-66x0vSaW-WKQRIanY0WBxCgqcHFldhao8ADVhxOFNSkO6cPumU79LJl7emskjRgiwwNsZZMU-1-t0eeuig=s945" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="263" data-original-width="945" height="178" src="https://blogger.googleusercontent.com/img/a/AVvXsEhdkF5escpMwlTcXeUqEx7q6V7-klBgwwQrQTsssByWEMXBgTH0sSQSVPFmWixIfFaJB7wA2A6k9zBK8Sbks5f2Ax7aJhNWPooQ7jYVAr3-66x0vSaW-WKQRIanY0WBxCgqcHFldhao8ADVhxOFNSkO6cPumU79LJl7emskjRgiwwNsZZMU-1-t0eeuig=w640-h178" width="640" /></a></div></i></div>
<p></p><h3 style="text-align: left;"><span style="font-family: inherit; font-size: medium;">URL Syntax</span><span style="font-family: inherit; font-size: small;"> </span></h3><p>
</p><div style="text-align: left;"><span style="font-family: inherit;">We need to compose a URL based on the above email template, which is to be included in a URL action.</span></div><div style="text-align: left;"><span style="font-family: inherit;">The syntax of the URL is as follows:</span></div><div style="text-align: left;"><span style="font-family: inherit;"><br /></span></div><div style="text-align: left;"><span style="font-family: inherit;">Mailto:<<span style="color: #ffa400;">Recipient</span> <span style="color: #ffa400;">Email</span>>?Subject=<<span style="color: #2b00fe;">Parameters.Subject</span>> <<span style="color: #ffa400;">Invoice ID</span>>&Body=<<span style="color: #2b00fe;">Parameters.Text1</span>></span></div><div style="text-align: left;"><span style="font-family: inherit;">$<SUM(<span style="color: #ffa400;">Amount</span>)> due on <<span style="color: #ffa400;">Due Date</span>>. <<span style="color: #2b00fe;">Parameters.Text2</span>>&Bcc=<<span style="caret-color: rgb(43, 0, 254); color: #2b00fe;">Parameters.</span><span style="color: #2b00fe;">Sender Email</span>></span></div><div style="text-align: left;"><span style="font-family: inherit;"><br /></span></div><div style="text-align: left;"><span style="font-family: inherit;">The orange fields are inserted from the data table. The purple fields are parameters. Note that all the orange fields need to be placed in the view of the worksheet, namely in Rows, Columns or in Mark's card.</span></div><p><span style="font-family: inherit;">The static texts are saved in a number of parameters. Note that <span style="color: #6aa84f;">%0A%0D</span> is the code for the line break. (</span><span style="caret-color: rgb(106, 168, 79); color: #6aa84f;">%0A, %0D, </span><span style="caret-color: rgb(106, 168, 79); color: #6aa84f;">%0A%0D </span><span style="caret-color: rgb(106, 168, 79);">seem </span><span style="caret-color: rgb(106, 168, 79);">all working as line break in both Mac and Windows. Not sure what the difference is.</span><span style="font-family: inherit;">)</span></p><div><span style="font-family: inherit;"><<span style="color: #2b00fe;">Parameters.Subject</span>>: Please send us invoice<br /><<span style="color: #2b00fe;">Parameters.Text1</span>>: Hi Team,<span style="caret-color: rgb(106, 168, 79); color: #6aa84f;">%0A%0D</span>In our record, there is<br /><<span style="color: #2b00fe;">Parameters.Text2</span>>: Please let us know if you can send us the invoice in a timely manner. <span style="caret-color: rgb(106, 168, 79); color: #6aa84f;">%0A%0D </span><span style="caret-color: rgb(106, 168, 79); color: #6aa84f;">%0A%0D</span>Thanks.<span style="caret-color: rgb(106, 168, 79); color: #6aa84f;">%0A%0D</span>Belinda<br /><<span style="caret-color: rgb(43, 0, 254); color: #2b00fe;">Parameters.</span><span style="color: #2b00fe;">Sender Email</span>>: belinda@somecompany.com</span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">By placing the static texts in parameters, it's easy to edit them. Otherwise, editing them in the action setup, can be tedious. </span></div><div><span style="font-family: inherit;"><br /></span></div><div><span style="font-family: inherit;">We could have used calculated fields for the static texts instead of parameters. It is more expensive that way because it adds more columns to the data set.</span></div>
<p></p><h3 style="text-align: left;"><span style="font-family: inherit; font-size: medium;">Action Setup</span></h3><p>
</p><div><span style="font-family: inherit;">The action setup is like this. We choose to run action on Menu.</span></div><div class="separator" style="clear: both; text-align: center;"><span style="font-family: inherit; margin-left: 1em; margin-right: 1em;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEifhLigQt23XzIRNKTBlyn4BLrvmoCpPG40vvoNXu--PDO-tsAq3S4Qhfj98X2zdgMojSLFQBbI3eFFtPuEcnmbmOePvDeMCYJQQwuUpWw-ygiObItmdk7lndaCzfIt6i27zl29c3OwO-8y9_YI7lp2yxnN3clzrz-iqFNWiaan359itzK8cYXoMLrK_A=s534" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="534" data-original-width="498" height="640" src="https://blogger.googleusercontent.com/img/a/AVvXsEifhLigQt23XzIRNKTBlyn4BLrvmoCpPG40vvoNXu--PDO-tsAq3S4Qhfj98X2zdgMojSLFQBbI3eFFtPuEcnmbmOePvDeMCYJQQwuUpWw-ygiObItmdk7lndaCzfIt6i27zl29c3OwO-8y9_YI7lp2yxnN3clzrz-iqFNWiaan359itzK8cYXoMLrK_A=w598-h640" width="598" /></a></span></div><div class="separator" style="clear: both; text-align: left;"><b>Caveats</b>: </div><div class="separator" style="clear: both; text-align: left;">1. If one of the fields is within <span style="color: #ffa400;">Measure Values</span>, do not insert it into the URL directly. Instead, place the field in question into <b>Detail</b> first. Then you can insert it into the URL.</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">2. Do not check the <b>Allow Multiple Values</b> box.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg0bA4RnnNjV6L6Bhd88nbRTqgAs02GREWyjUJPEStL14Asi4pdHNzkGQsa3phjffy4YVUeVsZcFXmoI7b2zHvI28cW1WfVtHLAtQMB4joFpaCdqEn0_AFovyWLOtbR9kPfLlBy5nptT4M5xltFAcZm31YKJy9_hyybTR_V5HUqvdHvKZwfXubpWXLibQ=s643" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="196" data-original-width="643" height="198" src="https://blogger.googleusercontent.com/img/a/AVvXsEg0bA4RnnNjV6L6Bhd88nbRTqgAs02GREWyjUJPEStL14Asi4pdHNzkGQsa3phjffy4YVUeVsZcFXmoI7b2zHvI28cW1WfVtHLAtQMB4joFpaCdqEn0_AFovyWLOtbR9kPfLlBy5nptT4M5xltFAcZm31YKJy9_hyybTR_V5HUqvdHvKZwfXubpWXLibQ=w640-h198" width="640" /></a></div><div class="separator" style="clear: both; text-align: left;">3. If one of the fields has a Null value, it will break the mailto URL. The remedy is wrapping the field with <span style="color: #3d85c6;">IFNULL</span>(<span style="color: #ffa400;">Field</span>, <span style="color: #ffa400;">Replacement_Value</span>). Do this for every field that may have a Null value. (Credit: <a href="https://twitter.com/EthanHahnEH" target="_blank">Ethan Hahn</a>)</div><div class="separator" style="clear: both; text-align: left;"><br /></div><div class="separator" style="clear: both; text-align: left;">4.For the native function <span style="caret-color: rgb(61, 133, 198); color: #3d85c6;">UNSERNAME</span>(), we can't use it directly in the URL. Instead we need to use its LOD form: {<span style="color: #3d85c6;">MAX</span>(<span style="color: #3d85c6;">UNSERNAME</span>())} (<a href="https://twitter.com/FlerlageKev/status/1504220193689673741" target="_blank">Source</a> by Kevin Flerlage)</div><h3 style="text-align: left;">Set up Tooltips</h3><span style="font-family: inherit;">To view the menu action link quickly in tooltips on data marks, we need to set up tooltips with the option of "<span style="color: #3d85c6;">On Hover - Show tooltips on hover</span>". Otherwise, we may not see the action link for email.</span><div class="separator" style="clear: both; text-align: center;"><span style="font-family: inherit; margin-left: 1em; margin-right: 1em;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjYUI9aiC8zTQxsScMAfhxtt0qNx-Zu2xVfxrhHAKfAJxqF-pdldljtnF2uaWVitdRqCh-00KVMWq3xxcS-L-79Kp7uTu3zQb6ZwGKtsVbsZoQDdvviGKAJ30RKCXLzF8koFCamOsLHSf9GhYgCIf6By2wNPseusmdlFwiX6cVU8uyr0IJcGTAfXhDZXg=s423" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="116" data-original-width="423" height="176" src="https://blogger.googleusercontent.com/img/a/AVvXsEjYUI9aiC8zTQxsScMAfhxtt0qNx-Zu2xVfxrhHAKfAJxqF-pdldljtnF2uaWVitdRqCh-00KVMWq3xxcS-L-79Kp7uTu3zQb6ZwGKtsVbsZoQDdvviGKAJ30RKCXLzF8koFCamOsLHSf9GhYgCIf6By2wNPseusmdlFwiX6cVU8uyr0IJcGTAfXhDZXg=w640-h176" width="640" /></a></div></span></div><div><span style="font-family: inherit;">The demo workbook can be downloaded <a href="https://public.tableau.com/app/profile/a.m.5517/viz/EmailTemplatefromTableau/Dashboard1?publish=yes">here</a>. The email link can be seen by hovering mouse on the amount.</span></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgmT2t1su426QTKCbr45tUzGhWiaYgwYYTrrHinyrv8EvBSetI3LCEb0zjfji4F0kg3M_tMYFnHDrzqqLZS_62sGbRqynJuIqbx9J1TtBmDVWStddrDUd4HcClmMdsglMFI9qAkYL2X8qDt0dZwuw-JqQylPLTjGj9hP2q7LsP9fJWGpGsWtV1mQBLf_g=s671" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: inherit;"><img border="0" data-original-height="283" data-original-width="671" height="272" src="https://blogger.googleusercontent.com/img/a/AVvXsEgmT2t1su426QTKCbr45tUzGhWiaYgwYYTrrHinyrv8EvBSetI3LCEb0zjfji4F0kg3M_tMYFnHDrzqqLZS_62sGbRqynJuIqbx9J1TtBmDVWStddrDUd4HcClmMdsglMFI9qAkYL2X8qDt0dZwuw-JqQylPLTjGj9hP2q7LsP9fJWGpGsWtV1mQBLf_g=w640-h272" width="640" /></span></a></div><div style="text-align: left;"><span style="font-family: inherit;">Try to send me an email if you wish.</span></div>
<p></p><h3 style="text-align: left;"><span style="font-family: inherit; font-size: medium;">Resulting Email</span></h3><p>
</p><div><span style="font-family: inherit;">An example of the resulting email is like:</span></div><div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj6M1vnogtWFNZGj0whTp2_uzde7Px5ctmUsTVTmLGcmIivj-fNOPN9sMyAhmqEjSVxP7sWT4-stDIK-hfLJN6Ll3xLbEPZ6O1ngN_V6LUANWYnCRQY52Mm_qEfobneuFgliqfxFX3Z9fxmC4hkPaeld7DuPki7iHd51FdVmSeFOxM0iklL86gxOK6HJQ=s821" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="238" data-original-width="821" height="186" src="https://blogger.googleusercontent.com/img/a/AVvXsEj6M1vnogtWFNZGj0whTp2_uzde7Px5ctmUsTVTmLGcmIivj-fNOPN9sMyAhmqEjSVxP7sWT4-stDIK-hfLJN6Ll3xLbEPZ6O1ngN_V6LUANWYnCRQY52Mm_qEfobneuFgliqfxFX3Z9fxmC4hkPaeld7DuPki7iHd51FdVmSeFOxM0iklL86gxOK6HJQ=w640-h186" width="640" /></a></div></div><div><span style="font-family: inherit;">I would like to thank Tableau Zen Master Jeffrey Schaffer for the line break code! Feel free to leave comments or contact me at <a href="https://twitter.com/aleksoft.">twitter@aleksoft.</a></span></div><p></p><p></p><p></p><p></p><p></p><p></p>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-22427148358035580472022-02-23T12:02:00.002-08:002022-02-24T04:42:55.665-08:00How to Make Phone Calls from Tableau via Cisco Jabber<p style="text-align: left;"><span style="font-family: inherit;">Belinda needs to call business partners in foreign countries regularly. She has a dashboard showing various deadlines that she has to monitor. If a deadline is overdue, she may need to talk to the partner in question. She has already integrated email in the dashboard via URL action. She came to me because she wanted to initiate phone calls right from the Tableau dashboard. BTW, the calls have to be made through Cisco Jabber, a network based phone software.<br /></span></p><p style="text-align: left;"><span style="font-family: inherit;">After a number of tests, I found a working solution for her. Here it is. We use Mac. For Windows users, you may find hints here.</span></p><p></p><p style="text-align: left;"><span style="font-family: inherit;">1. Set the preferences in Jabber to make it the default. Otherwise, Mac will open FaceTime to call.</span></p><span style="font-family: inherit;"><div style="text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhRTMDoHYSGTyeYUlM3c8FktP6AXCrE7Fn8TAc1hX4VkfLsydDqF1hdyCvBPOGqvcSmMsMJYpBgQMHbtENy2KoXTcpurSj_dP0Yaox_9FLejDKZ_phAuVimA2YB4vK4RN9sv9LsN4bVoV4GEC-QMXG2LVbLQOO4ejfFvjUc3PQvcjwMFJ6gKtjvUjJhig=s1634" style="font-family: inherit; margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="782" data-original-width="1634" height="306" src="https://blogger.googleusercontent.com/img/a/AVvXsEhRTMDoHYSGTyeYUlM3c8FktP6AXCrE7Fn8TAc1hX4VkfLsydDqF1hdyCvBPOGqvcSmMsMJYpBgQMHbtENy2KoXTcpurSj_dP0Yaox_9FLejDKZ_phAuVimA2YB4vK4RN9sv9LsN4bVoV4GEC-QMXG2LVbLQOO4ejfFvjUc3PQvcjwMFJ6gKtjvUjJhig=w640-h306" width="640" /></a></div>2. <span>Create a URL action in Tableau. Make sure you have a phone number column in the data set. The numbers start with 9011 instead of +. Insert the column in the URL action settings. Note that the URL syntax is like </span></span><div><ul style="text-align: left;"><li><span style="font-family: inherit;">Tel:9011<<span style="color: #ffa400;">Country Code</span>><<span style="color: #ffa400;">Phone Number</span>></span></li></ul><div><div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjQ4Mb-o5ONCtHEdZMtUuSjk1ER-ABpxmgMUvPtviLkhB8foVP_eOTE3tJx_iOwmKUdNVKKZO56ZnHMnqDBXIvCOtWbrexNZ3MbvwRbcC4VyA39xs1VD5ud6AePNhU7bCnpaljhyhr3JvGKnJPjESj6s025R191LoPu7BjKYtvNAvgy47wsLBqlJ_tOiQ=s601" style="margin-left: 1em; margin-right: 1em;"><span style="font-family: inherit;"><img border="0" data-original-height="597" data-original-width="601" height="638" src="https://blogger.googleusercontent.com/img/a/AVvXsEjQ4Mb-o5ONCtHEdZMtUuSjk1ER-ABpxmgMUvPtviLkhB8foVP_eOTE3tJx_iOwmKUdNVKKZO56ZnHMnqDBXIvCOtWbrexNZ3MbvwRbcC4VyA39xs1VD5ud6AePNhU7bCnpaljhyhr3JvGKnJPjESj6s025R191LoPu7BjKYtvNAvgy47wsLBqlJ_tOiQ=w640-h638" width="640" /></span></a></div></div></div><p style="text-align: left;"><span style="font-family: inherit;">Voila. </span></p><p style="text-align: left;"><span style="font-family: inherit;"><span style="background-color: white; caret-color: rgb(51, 51, 51); text-align: justify;">Feel free to leave comments or contact me at</span><a href="https://twitter.com/aleksoft" style="display: inline; outline: none; text-align: justify; text-decoration: none;" target="_blank"> twitter@aleksoft</a><span style="background-color: white; caret-color: rgb(51, 51, 51); text-align: justify;">.</span></span></p><p></p></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-73424231351129776202022-02-07T02:08:00.011-08:002022-02-08T13:39:20.939-08:00Labeling Trellis Chart via Stacked/Shared Axis<p>This post is about labeling a trellis chart that's already in dual axis.</p><p>In <a href="https://vizdiff.blogspot.com/search/label/Trellis%20Chart" target="_blank">earlier posts</a> on labeling trellis chart, we use one axis for the labeling function. Many times, the chart is already in dual axis (both axis are taken). What should we do then?</p><p>Here we are going to show two approaches about labeling a dual-axis trellis chart: </p><p></p><ol style="text-align: left;"><li>Labeling with Stacked Axis</li><li>Labeling with Shared Axis</li></ol><div><h3>Labeling with Stacked Axis</h3></div><p></p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgL_QPlkScrsoff4e3kn1yqs3uIIAKhWx5SPJN85wkdaWzYCIr3batgCJJiSJYN3Spp14kfk22tFzVcdaRteujLO_cHZfdyKZkes3VOmd6EoVyDeOPrOq2NI8ONMTvZktCip6LjLRp5qGTc6Fzyy-DRe0A7O5KJDUZluvJy3db6ohGtLduIiCIvbPp8Xg=s1040" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="791" data-original-width="1040" height="488" src="https://blogger.googleusercontent.com/img/a/AVvXsEgL_QPlkScrsoff4e3kn1yqs3uIIAKhWx5SPJN85wkdaWzYCIr3batgCJJiSJYN3Spp14kfk22tFzVcdaRteujLO_cHZfdyKZkes3VOmd6EoVyDeOPrOq2NI8ONMTvZktCip6LjLRp5qGTc6Fzyy-DRe0A7O5KJDUZluvJy3db6ohGtLduIiCIvbPp8Xg=w640-h488" width="640" /></a></div><div>This is a simple approach. Please refer to <a href="https://vizdiff.blogspot.com/2022/01/placing-multiple-labels-in-trellis-chart.html" target="_blank">this post</a> on composing a label and on the formula for <span style="color: #ffa400;">Label Height</span>. Let's place the <span style="color: #ffa400;">Label Height</span> pill on the <b>Rows</b> to the left of the dual-axis measure pills. Then each trellis cell is split in halves. The labels will be placed in the top half. The chart is in the lower half. We can see that two axis are stacked up.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjLN-DzAKLRaHoQyBauGjqC5O1N3Bgn3U5bNdQbN-rV6_Kv_FEEJwiMqpKP0_6zg0rcVnTiQ--fpr4IN38rW0Jhb_PTSr6ZTzdodB_6P46yYnc6epxP_2yskWBqzIJo_y6BtKi-AkSLoDS5xnos8j16_ZG4ujsTrCcCgfl4NbSR9ejTLYgAfqvA9Ytk2A=s1012" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="571" data-original-width="1012" height="362" src="https://blogger.googleusercontent.com/img/a/AVvXsEjLN-DzAKLRaHoQyBauGjqC5O1N3Bgn3U5bNdQbN-rV6_Kv_FEEJwiMqpKP0_6zg0rcVnTiQ--fpr4IN38rW0Jhb_PTSr6ZTzdodB_6P46yYnc6epxP_2yskWBqzIJo_y6BtKi-AkSLoDS5xnos8j16_ZG4ujsTrCcCgfl4NbSR9ejTLYgAfqvA9Ytk2A=w640-h362" width="640" /></a></div>In the formatting panel, we can remove the line between two halves by lowering the<b> Row Divider Level</b>.<div class="separator" style="clear: both; text-align: center;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhQVzsaFMtjrln5bCTJC-B2jEJ0n9r0vjEP6y-jEd9HyR1yfzR3xEThTF43vRVMFcig88ySZxjTBKaI35LBNVnmMWG_Tc6ybmepTeoUHtHdXsiHeOBHpT5A247gdikRJMIwhS3yiiXfasdV6ERRFK7zHxVJWx1lp0-YkG7o9GvYBxtaarfHaQbmEvZOqg=s1000" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="578" data-original-width="1000" height="370" src="https://blogger.googleusercontent.com/img/a/AVvXsEhQVzsaFMtjrln5bCTJC-B2jEJ0n9r0vjEP6y-jEd9HyR1yfzR3xEThTF43vRVMFcig88ySZxjTBKaI35LBNVnmMWG_Tc6ybmepTeoUHtHdXsiHeOBHpT5A247gdikRJMIwhS3yiiXfasdV6ERRFK7zHxVJWx1lp0-YkG7o9GvYBxtaarfHaQbmEvZOqg=w640-h370" width="640" /></a></div></div><div>The only drawback of this approach is that the dynamic range of the chart is reduced by half. To some, this may be good enough.</div><div><h3>Labeling with Shared Axis</h3></div><h3 style="text-align: left;"><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEgM61zu6KwYRoxlBjQwuUXL3_3PuAbsW5EUjOCS32WkJRGcFsZDPxM2Ao35xVTWcwzBWSt-n5FF3SOn7lxXjC8lT7mgDkXHr3HZCRFbiNXljwhh07LzU7KtvI405GNsbv8oqEJkN5rY1AuaFatgYT_EJIZGWgjwbKzKRPaNv3j87GL7f2EmSbrYg8PrQA=s1037" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="786" data-original-width="1037" height="488" src="https://blogger.googleusercontent.com/img/a/AVvXsEgM61zu6KwYRoxlBjQwuUXL3_3PuAbsW5EUjOCS32WkJRGcFsZDPxM2Ao35xVTWcwzBWSt-n5FF3SOn7lxXjC8lT7mgDkXHr3HZCRFbiNXljwhh07LzU7KtvI405GNsbv8oqEJkN5rY1AuaFatgYT_EJIZGWgjwbKzKRPaNv3j87GL7f2EmSbrYg8PrQA=w640-h488" width="640" /></a></div></h3><div>In this one, we will use the <span style="color: #ffa400;">Measure Names</span>/<span style="color: #ffa400;">Measure Values</span> <a href="https://www.google.com/search?client=safari&rls=en&q=Measure+Names%2FMeasure+Values&ie=UTF-8&oe=UTF-8" target="_blank">approach</a> where two or more measures can share the same axis. First we create a shared axis between <span style="color: #ffa400;">Label Height First/Last</span> and the key measure (<span style="color: #ffa400;">Unemployment Rate</span>), before dual-axis-ing with another measure. This approach, the dynamic range of the chart is not affected, given the same canvas space.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEh80xhJAYlkQBmd9Z3j_LrbOjelwyeY3lLUlNE6NUXAel5eG5blFfTJviDm17iI8YFtM021qpqTwBmJu3mylejpBS9Xj1Gq2SHM9jiBvjupXe0exwxYXd1siViStDG4AhBuaJ5iFC-UvBjX_XIs2GS6bQSXbU_Fk4jgFNB-OVCTYXAlYWG7GhG-b4DX-A=s795" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="554" data-original-width="795" height="448" src="https://blogger.googleusercontent.com/img/a/AVvXsEh80xhJAYlkQBmd9Z3j_LrbOjelwyeY3lLUlNE6NUXAel5eG5blFfTJviDm17iI8YFtM021qpqTwBmJu3mylejpBS9Xj1Gq2SHM9jiBvjupXe0exwxYXd1siViStDG4AhBuaJ5iFC-UvBjX_XIs2GS6bQSXbU_Fk4jgFNB-OVCTYXAlYWG7GhG-b4DX-A=w640-h448" width="640" /></a></div><div>The <span style="color: #ffa400;">Label Height Scaled</span> field is created as follows:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiy48V7JTFB1ev8SPks7C3ZXvN1iBhm5wJ8IwD4NLsmsY4GNXhcrs_XZp2qW0Jmn0aA0p7a6tHz4t--FOv7GWBEQcEF0tnmpCbmrjdgCcI_r9NueYHzWDTM8Dz-Resk8WHJkPz47QYnQUbjblvI6of1vXVTQgG9rGDvay5DNKs5yoVtuxmujaeKmug7AQ=s425" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="98" data-original-width="425" height="150" src="https://blogger.googleusercontent.com/img/a/AVvXsEiy48V7JTFB1ev8SPks7C3ZXvN1iBhm5wJ8IwD4NLsmsY4GNXhcrs_XZp2qW0Jmn0aA0p7a6tHz4t--FOv7GWBEQcEF0tnmpCbmrjdgCcI_r9NueYHzWDTM8Dz-Resk8WHJkPz47QYnQUbjblvI6of1vXVTQgG9rGDvay5DNKs5yoVtuxmujaeKmug7AQ=w640-h150" width="640" /></a></div><div>The first window function is to compute the max value of <span style="color: #ffa400;">Unemployment Rate</span> across the entire <span style="color: #ffa400;">Date</span> and <span style="color: #ffa400;">Country Name</span>. Then it is scaled by a parameter that is >=1. This formula will guarantee that the data marks for labels will be no lower than any data mark in a chart. This fact will be used in label settings where we only show those labels at the max <span style="color: #ffa400;">Measure Values</span>.</div><div><br /></div><div>The field <span style="color: #ffa400;">Label Height First/Last</span> is as follows, which keeps only the necessary data points.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEirsjobcXsCBF-P2nqoejCV1_79TL3ISFFRecNvria5tv5lZbvt8FHl36p6vbOHJe5QzPteWo2lgIDBoRm9tFcrs6TSPmtPpWOK2tApwrtQuxV86wjdSVIrh60Za84UiUtwN33UttOr6kMTnGmLw1Dfyl5PjVe_VrztrgevK6Lb21LYU1U5hzpIcvxhqQ=s483" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="96" data-original-width="483" height="128" src="https://blogger.googleusercontent.com/img/a/AVvXsEirsjobcXsCBF-P2nqoejCV1_79TL3ISFFRecNvria5tv5lZbvt8FHl36p6vbOHJe5QzPteWo2lgIDBoRm9tFcrs6TSPmtPpWOK2tApwrtQuxV86wjdSVIrh60Za84UiUtwN33UttOr6kMTnGmLw1Dfyl5PjVe_VrztrgevK6Lb21LYU1U5hzpIcvxhqQ=w640-h128" width="640" /></a></div>We can use the <span style="color: #674ea7;">Scale</span> parameter to fine tune the dynamic range of the chart.<br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhctKCffcE6PoTPMbOutQkTIr7mz1Bm_DXZpPFJlpnIifhT8aUqC4z4KxGDg_WCDXP-u-q_faw3ewcJjJbZTOHRj4z2a3DDvGjqNb4ddYr-wfF18I_qaUAAeQcYVi5P8ysNGADMqsbcF3fY1Vhs9D0T9pZmvsCHG2fgX6cY_gd-w6xzEBrwJabjmmvJKA=s706" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="282" data-original-width="706" height="256" src="https://blogger.googleusercontent.com/img/a/AVvXsEhctKCffcE6PoTPMbOutQkTIr7mz1Bm_DXZpPFJlpnIifhT8aUqC4z4KxGDg_WCDXP-u-q_faw3ewcJjJbZTOHRj4z2a3DDvGjqNb4ddYr-wfF18I_qaUAAeQcYVi5P8ysNGADMqsbcF3fY1Vhs9D0T9pZmvsCHG2fgX6cY_gd-w6xzEBrwJabjmmvJKA=w640-h256" width="640" /></a></div><div>Note that in the <b>Label</b> setting, we need to select <span style="color: #ffa400;">Measure Values</span> and set the labels to be at the max. Allow labels to overlap other marks.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj8Bz9KeSZjycMQv0adVwh0iDctd9F9HlRBew3_YteZk_G4F6ExtbTVbLlVjfyCqbwcTE3vbhFnzIgkuPhYz08BGgzo-KcG1z4Egk5wtRbuUcZ7f1Ax0dVF8CW-ieKS5kRIsfkIRvUS22JMF8gBHGPPZbddp_JcOWMXHeuyLOnBI_CeS7d1hgNy-zY5Mg=s553" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="553" data-original-width="380" height="640" src="https://blogger.googleusercontent.com/img/a/AVvXsEj8Bz9KeSZjycMQv0adVwh0iDctd9F9HlRBew3_YteZk_G4F6ExtbTVbLlVjfyCqbwcTE3vbhFnzIgkuPhYz08BGgzo-KcG1z4Egk5wtRbuUcZ7f1Ax0dVF8CW-ieKS5kRIsfkIRvUS22JMF8gBHGPPZbddp_JcOWMXHeuyLOnBI_CeS7d1hgNy-zY5Mg=w440-h640" width="440" /></a></div><div>We showed that even for a dual-axis-ed trellis chart, we can still label them.</div><div><br /></div>The demo workbook can be downloaded <a href="https://public.tableau.com/app/profile/a.m.5517/viz/LabelingTrellisChartviaStackedSharedAxis/TrelliswSingleAxis?publish=yes" target="_blank">here</a>. <div><br /></div><div>Feel free to leave comments or contact me at<a href="https://twitter.com/aleksoft" target="_blank"> twitter@aleksoft</a>.</div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-15164258810883113692022-02-01T01:03:00.006-08:002023-10-23T01:00:33.288-07:00Sizing a Trellis Chart in TableauIn online Tableau literature, I noticed that most people referred to Zen Master Chris Love's formula in <a href="https://www.theinformationlab.co.uk/2014/10/06/dynamic-visualisations-size-index/" target="_blank">a 2014 post</a> about the size of a trellis chart.<div><br /></div><div>For the <span style="color: #ffa400;">Column</span> (<span style="color: #ffa400;">X axis</span>) and <span style="color: #ffa400;">Row</span> (<span style="color: #ffa400;">Y axis</span>), the formula are</div><div><ul style="text-align: left;"><li><span style="color: #ffa400;">X axis</span> = (<span style="color: #3d85c6;">Index</span>()-1)%<span style="color: #0b5394;">Int</span>(<span style="color: #0b5394;">Sqrt</span>(<span style="color: #0b5394;">Size</span>()))</li><li><span style="color: #ffa400;">Y axis</span> = <span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Int</span>((<span style="caret-color: rgb(61, 133, 198); color: #3d85c6;">Index</span>()-1)/<span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Int</span>(<span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Sqrt</span>(<span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Size</span>())))</li></ul></div><div><span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Size</span>() is related to the number of trellis cells. The formula are based on <span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Sqrt</span>(<span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Size</span>()) and will result in asymptomatically the same numbers of columns and rows, or a square-like trellis chart. This is not the only option though. </div><div><br /></div><div>We can have quite different numbers of columns and rows, thus various sizes of trellis chart. </div><div><br /></div><div>In theory, we can pick an arbitrary integer as the <span style="caret-color: rgb(11, 83, 148); color: #674ea7;"># of columns</span> <= <span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Size</span>(). Then we use the following formula for X axis and Y axis:</div><div><ul style="text-align: left;"><li><span style="color: #ffa400;">X axis</span> = (<span style="color: #3d85c6;">Index</span>()-1)%<span style="caret-color: rgb(11, 83, 148);">[<span style="color: #674ea7;"># of columns</span>]</span></li><li><span style="color: #ffa400;">Y axis</span> = <span style="caret-color: rgb(11, 83, 148); color: #0b5394;">Int</span>((<span style="caret-color: rgb(61, 133, 198); color: #3d85c6;">Index</span>()-1)/<span style="caret-color: rgb(11, 83, 148);">[</span><span style="caret-color: rgb(11, 83, 148); color: #674ea7;"># of columns</span><span style="caret-color: rgb(11, 83, 148);">]</span>)</li></ul></div><div>In the following example, we got a dimension <span style="color: #ffa400;">Country Name</span> having 36 countries. Assume we pick the <span style="caret-color: rgb(11, 83, 148); color: #674ea7;"># of columns</span> to be 2, 3, 4, 5, 6. The corresponding # of rows will be 18, 12, 9, 8, 6. We thus got 5 options of trellis chart for the same data set.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg1N58J2tYABXII3vSlbugZOl509MCSV9EcH7s6zayKkn_PudgEZnhabKcB_18uZsEwAuRHlYmZ-DYpit-voxdnABu7I0uJdEUd86_8BHXgHQiUP5p71ot6VeOb1Ss79LODGBy9uosCQMeEcN4M1HIVtG8b67UwR3eVx4ohl3sdS15UbtL7pqhbDteO4g=s1096" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="1096" data-original-width="683" src="https://blogger.googleusercontent.com/img/a/AVvXsEg1N58J2tYABXII3vSlbugZOl509MCSV9EcH7s6zayKkn_PudgEZnhabKcB_18uZsEwAuRHlYmZ-DYpit-voxdnABu7I0uJdEUd86_8BHXgHQiUP5p71ot6VeOb1Ss79LODGBy9uosCQMeEcN4M1HIVtG8b67UwR3eVx4ohl3sdS15UbtL7pqhbDteO4g=s16000" /></a></div>You can download the demo workbook <a href="https://public.tableau.com/app/profile/a.m.5517/viz/SizingaTrellisChart/Dashboard4?publish=yes" target="_blank">here</a> and test various size options for a trellis chart. The trellis chart is inspired by <a href="https://twitter.com/ashthewall19/status/1483127832100614152" target="_blank">Ashwin Shankar's design</a>.<br /><div><div class="article-content entry-content" itemprop="articleBody" style="clear: both; color: #333333; line-height: 1.4; margin: 10px auto 5px; outline: currentcolor; padding: 0px; text-align: justify;"><div style="margin: 0px; outline: currentcolor; padding: 0px;"><div style="margin: 0px; outline: currentcolor; padding: 0px;"><div style="margin: 0px; outline: currentcolor; padding: 0px; text-align: left;"><span face=""Helvetica Neue Light", HelveticaNeue-Light, "Helvetica Neue", Helvetica, Arial, sans-serif" style="background-color: white; text-align: justify;">Feel free to leave comments below or contact me at </span><a href="https://twitter.com/aleksoft" style="color: #009eb8; display: inline; font-family: "Helvetica Neue Light", HelveticaNeue-Light, "Helvetica Neue", Helvetica, Arial, sans-serif; outline: currentcolor; text-align: justify; text-decoration: none;" target="_blank">twitter @aleksoft</a><span face=""Helvetica Neue Light", HelveticaNeue-Light, "Helvetica Neue", Helvetica, Arial, sans-serif" style="background-color: white; text-align: justify;">.</span></div></div></div></div></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0tag:blogger.com,1999:blog-8934279937363431235.post-7272864352896318142022-01-30T01:43:00.000-08:002022-01-30T01:43:47.049-08:00Placing Multiple Labels in Trellis Chart<p>Sharon left a message in my last post on <a href="https://vizdiff.blogspot.com/2022/01/labeling-trellis-chart-anywhere-in.html" target="_blank">Labeling Trellis Chart Anywhere</a> asking whether we can have one label on the left and another on the right per trellis chart cell in Tableau. Yes we can. Below we will show how to place multiple labels within a trellis cell.</p><h3 style="text-align: left;">Label's Horizontal Positions </h3><p>Assume our data is a time series and the horizontal dimension is <span style="color: #ffa400;">Date</span>. Then here are a few examples.</p><p></p><ul style="text-align: left;"><li>Label on the left is defined by <span style="color: #3d85c6;">First</span>()=0.</li><li>Label on the right is defined by <span style="color: #3d85c6;">Last</span>()=0.</li><li>Label in the center is defined by <span style="color: #3d85c6;">Index</span>()=<span style="color: #3d85c6;">Int</span>(<span style="color: #3d85c6;">Size</span>()/2)</li></ul><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhGc30BcVwcFUf5Sy0PRa_1hAGumxlUAZgNTfQT9xpvpDFWqaQir_U1_nGwNl4LR5h3qvP-4G_r4gAghYWRFZTCp3zJXHhS2kKqJgwP0jVLj1CrqLuj9OO6MUA5eJ49k5M3u-YI9vG5hUIqTozM6KWSv3EBuAiu-ctdyRBhK9sC4gNIjbD9fza9Wh3-8A=s790" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="790" data-original-width="741" height="640" src="https://blogger.googleusercontent.com/img/a/AVvXsEhGc30BcVwcFUf5Sy0PRa_1hAGumxlUAZgNTfQT9xpvpDFWqaQir_U1_nGwNl4LR5h3qvP-4G_r4gAghYWRFZTCp3zJXHhS2kKqJgwP0jVLj1CrqLuj9OO6MUA5eJ49k5M3u-YI9vG5hUIqTozM6KWSv3EBuAiu-ctdyRBhK9sC4gNIjbD9fza9Wh3-8A=w601-h640" width="601" /></a></div><p></p><p>Here are some of the labels we use in the above trellis chart:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiBBT4lBKlzDGy7_La1ddROMshiyVsgLUw8pWkdj4sxQLml3JQ9y5kUEuLlqMo4N1QKs9xxirooXSmZ5QWajWusMeV1f5bJxS-J1thI1mRtwxVfU0FClxZSGtNYqro5Wz8phSYKMYo9dcqo_fX7IlhDkZQdEnKiV9m_KF_CY4LtcjgkvZi_tGnAVdu2QQ=s467" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="100" data-original-width="467" src="https://blogger.googleusercontent.com/img/a/AVvXsEiBBT4lBKlzDGy7_La1ddROMshiyVsgLUw8pWkdj4sxQLml3JQ9y5kUEuLlqMo4N1QKs9xxirooXSmZ5QWajWusMeV1f5bJxS-J1thI1mRtwxVfU0FClxZSGtNYqro5Wz8phSYKMYo9dcqo_fX7IlhDkZQdEnKiV9m_KF_CY4LtcjgkvZi_tGnAVdu2QQ=s16000" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEiDbpDq9Z_5onOZhh36u3SJQ3I19asDgU6_FVmv8zI1-WjKcanvxUlaN6bT-QwUTalz6K42yD9veIN28qgwJLUZrgkGopdSGd_SqoUdF_JpoRUEbJ2E6ObSlQ5RLPjoggqOJH0IEwwbMQ1tI5-PSxLPs-nw8vNBEF70dr592MkzzqzoiL60HzhFnu-BuA=s437" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="129" data-original-width="437" src="https://blogger.googleusercontent.com/img/a/AVvXsEiDbpDq9Z_5onOZhh36u3SJQ3I19asDgU6_FVmv8zI1-WjKcanvxUlaN6bT-QwUTalz6K42yD9veIN28qgwJLUZrgkGopdSGd_SqoUdF_JpoRUEbJ2E6ObSlQ5RLPjoggqOJH0IEwwbMQ1tI5-PSxLPs-nw8vNBEF70dr592MkzzqzoiL60HzhFnu-BuA=s16000" /></a></div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEjv344IPKZqly-98Fuq4p0y5DqhtHjgH4718fOdGgTOJbNu2juHCPQ7XC40l3DTMEN7I7__Era3V0msiY5cqtFAptx72YQlZUPwBw6UUy9h09fIrzhrnGqPL2albqHobfyFviKfP3P4MvbQvKqTV3WcZqDEzEWkE5FsJxd3o_G50HkhfFtWEq2n_CKIHQ=s238" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="90" data-original-width="238" height="90" src="https://blogger.googleusercontent.com/img/a/AVvXsEjv344IPKZqly-98Fuq4p0y5DqhtHjgH4718fOdGgTOJbNu2juHCPQ7XC40l3DTMEN7I7__Era3V0msiY5cqtFAptx72YQlZUPwBw6UUy9h09fIrzhrnGqPL2albqHobfyFviKfP3P4MvbQvKqTV3WcZqDEzEWkE5FsJxd3o_G50HkhfFtWEq2n_CKIHQ" width="238" /></a></div><div class="separator" style="clear: both; text-align: left;">In the label editor, we compose the labels. Both the left label and right labels are placed on the first row. Depending on horizontal positions, some labels are on when the others are off. The 2nd row has only labels on the right. The alignment is set to be center-aligned.</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEhwix-cWXTF3tcGl9pVsAxIumol984G2us3pyr-f2dXpUAwH_Jf9bzb9hbf5HsE6TZGNsToQbg317d-J53P75KMzb8QFFQJstIHivifnjTJbkf5hj4EXPh_d_uF1AWsD2R-PXpsGx3pOO2l44VmcmgyI50vkPU2bPL188YquNOKkLrtlioFPd1sbKeLIA=s586" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="142" data-original-width="586" src="https://blogger.googleusercontent.com/img/a/AVvXsEhwix-cWXTF3tcGl9pVsAxIumol984G2us3pyr-f2dXpUAwH_Jf9bzb9hbf5HsE6TZGNsToQbg317d-J53P75KMzb8QFFQJstIHivifnjTJbkf5hj4EXPh_d_uF1AWsD2R-PXpsGx3pOO2l44VmcmgyI50vkPU2bPL188YquNOKkLrtlioFPd1sbKeLIA=s16000" /></a></div><div class="separator" style="clear: both; text-align: center;"><br /></div>How many labels can we place within a trellis cell? Probably more than we need. We can add as many labels as there are dates or months. <h3 style="text-align: left;">Label's Vertical Positions</h3><div>In the previous trellis chart, both labels have equal height at the top. The height of both is defined by the following function:</div><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEj1BDDPDXEQ8GwG82AImBJi_gAvM-2PfOyZXfTwNGCpTsC7CvLzzkABETEfVUmocQTe34dRdWGnlW_lKH4JwDWDmgCdW_Az6PrkmgToAXXfCgtzrJcwVOo0yWeF7AAMLGWGYwm6OSLhstNKoMolRaQiBTFEZ4kts9CRMkGRclteHU6ODbvh54SlbSCT6g=s246" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="95" data-original-width="246" height="95" src="https://blogger.googleusercontent.com/img/a/AVvXsEj1BDDPDXEQ8GwG82AImBJi_gAvM-2PfOyZXfTwNGCpTsC7CvLzzkABETEfVUmocQTe34dRdWGnlW_lKH4JwDWDmgCdW_Az6PrkmgToAXXfCgtzrJcwVOo0yWeF7AAMLGWGYwm6OSLhstNKoMolRaQiBTFEZ4kts9CRMkGRclteHU6ODbvh54SlbSCT6g" width="246" /></a></div>Note that the vertical axis is fixed from 0 to 1. Since we know there are only two labels and their exact positions, we could alternatively write <span style="color: #ffa400;">Label Height 1</span> as<div><ul style="text-align: left;"><li>If <span style="color: #3d85c6;">First</span>()=0 or <span style="color: #3d85c6;">Last</span>()=0 Then 1 End</li></ul><div>The horizontal grid is defined already by <span style="color: #ffa400;">Date</span>. We will show how to use <span style="color: #ffa400;">Label Height 2</span> to define the vertical positions of labels. <br /><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEg_XeZf1jiD8Cs_b8GatNyS3Vu46g02-ZDabit8XvudTuoXJUnBZRxE6DfK8SP2blD2Z6ucU3BNuPtgAKjF-7MXj-yBOP5nKdpMdL_L5X8Q4zBm5jCDsi9QhbL6E6C-J1bY4sKztOdTLm3e8fq_tN12_pchqSbNZ-K7j-8TjB5WFIR0UFxjsRsKAH1DtA=s1041" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="791" data-original-width="1041" height="488" src="https://blogger.googleusercontent.com/img/a/AVvXsEg_XeZf1jiD8Cs_b8GatNyS3Vu46g02-ZDabit8XvudTuoXJUnBZRxE6DfK8SP2blD2Z6ucU3BNuPtgAKjF-7MXj-yBOP5nKdpMdL_L5X8Q4zBm5jCDsi9QhbL6E6C-J1bY4sKztOdTLm3e8fq_tN12_pchqSbNZ-K7j-8TjB5WFIR0UFxjsRsKAH1DtA=w640-h488" width="640" /></a></div><p>In the above chart, we have 2 labels per trellis cell. We write <span style="color: #ffa400;">Label Height 2</span> as follows:</p><div class="separator" style="clear: both; text-align: center;"><a href="https://blogger.googleusercontent.com/img/a/AVvXsEibngwb4kqf6bGAPjbEVkRzUUMXH7q33HgMDJ11r3WOtg8Er6ESE2Y0Oife-L2MkCdA7JHQo-7uqlIVLsm1e5REdh_UXzrIr3ji1apBLcrth-Swm_TOwv709obn_8E_T_zDz7xDxD8kVpE8N3xZOXP9Yk1B3GBHDP21L0LNtDiN1TPkDPpSaCHFWiABSQ=s306" imageanchor="1" style="margin-left: 1em; margin-right: 1em;"><img border="0" data-original-height="126" data-original-width="306" height="126" src="https://blogger.googleusercontent.com/img/a/AVvXsEibngwb4kqf6bGAPjbEVkRzUUMXH7q33HgMDJ11r3WOtg8Er6ESE2Y0Oife-L2MkCdA7JHQo-7uqlIVLsm1e5REdh_UXzrIr3ji1apBLcrth-Swm_TOwv709obn_8E_T_zDz7xDxD8kVpE8N3xZOXP9Yk1B3GBHDP21L0LNtDiN1TPkDPpSaCHFWiABSQ" width="306" /></a></div>Note that the height for the labels in the center is -0.2. The vertical axis is fixed between -0.2 to 1.</div><h3 style="text-align: left;">Conclusion</h3><div>We can place multiple labels per trellis cell. And again we can label them anywhere. </div><div><br /></div><div>Feel free to download <a href="https://public.tableau.com/app/profile/a.m.5517/viz/MultiLabelTrellisChart/MultiLabelTrellis?publish=yes" target="_blank">the demo workbook</a>. If you have questions, leave comments or contact me at <a href="https://twitter.com/aleksoft" target="_blank">twitter @aleksoft</a>.</div></div>Alexander Mouhttp://www.blogger.com/profile/08750031316598620047noreply@blogger.com0