_edited_edited.png)
My Fellow Tableau beginners, I recently started learning to Viz in Tableau. After doing some basic charts with sample superstore data set, I started to learn about calculations, parameters, actions, LOD, and much more. Though I understood some concepts logically and practically I didn’t put all of them to use. So in my mind, I had some uncertainty regarding some topics. When I googled to clear some of my doubts, I came across a website called https://www.workout-wednesday.com/. This is an active vibrant community giving out weekly challenges in both Tableau and Power BI. If you love solving puzzles and reading detective novels, then definitely I bet you would love these challenges. I took 2 basic beginner-level tableau challenges that used the same sample superstore data set. This post is all about how I tried to solve those challenges. The first challenge was originally posted in 2018 at https://www.workout-wednesday.com/workoutwednesday-week4/.
Their requirement was
1. Show a table of sales by customers and years.
2. Only include customers whose sales have increased each year from 2018 -2021(I used the latest sample super store data set year)
3. Sort them by their 2021 sales amount.
With a twist: You cannot use any LOD calculation or any table calculation. So you have to try to recreate the below visual exactly. I encourage the tableau beginners to try this challenge first before finding the solution below.
Image source: Author
Step 1: Build the text table. For this, we need a minimum of 1 or more dimensions and 1 or more measures. Place the Year(order date) in the columns shelf and Customer Name (add all customer names do not filter here) in the rows shelf and place the SUM(Sales) under Text in the Marks pane. Now the text table is ready but not done. We need to refine or narrow down to recreate the exact visual as in the challenge based on the requirement. The table now should look like the below picture.
Image source: Author
Step 2: Click on the sort symbol for the year 2021 in the text table. By default when you click once Tableau sorts them in descending order. Click twice for descending and the third time when you click it de-sorts. We just need one click for our sort now. The text table now looks like the below picture.
Image source: Author
But look carefully there are some customers who have not ordered every year and some customers have inconsistent sales growth (data shows an increase in the previous year’s sales and a then decrease in the next year). So we have to filter out those values.
Step 3: Now our next step is to filter out all the Null data (customers who have not ordered every year) in the table. Now create calculation that lists the customers who have ordered for the year 2018.
Image source: Author
Similarly create separate calculations for the years 2019, 2020, and 2021.
Step 4: Now filter the list of customers whose sales in the year 2018 <2019<2020<2021. Click the Filter in the Customer Name pill under the rows. Go to the conditions tab and put the logic in the formula shown in the below picture. Click apply to see a preview of the table and ok.
Image source: Author
Boom!! We got the table!! Wait. It’s still not over. We have one more step. Go to the title that says “sheet” and right-click. Now edit the title as “These customers just go up and up!!”, change the font color to blue and increase the font size. Right click-on the sum(sales) pill under the marks pane. Click format and under the pane tab, go to numbers. Choose Currency custom from the drop down, and make the decimal places 0 points. Tada!! We did it.
Image source: Author
If you are still unclear you can download my tableau public workbook from the link here and try it again. In the first challenge, I learned to filter the null values without using any NULL function. Even with simple breakdowns and logic, we can filter the list of customers whose sales went up every year.
My second challenge was also originally posted in Week 7: Min and Max Sales by Month – Workout Wednesday (workout-wednesday.com) using the same sample super store data set we need to recreate the below text table.
Image source: Author
The requirement was to have Sub-Categories sorted in Descending order by Total Sales in the Year, Highlight the MIN Sales in Red, Max Sales in Blue for each month, and allow users to Filter by Year and/or Category. An additional note to pay attention to formatting was also given.
Step 1: Place MONTH(Order Date) in the column shelf and Sub-Category in the row shelf. Drag SUM(Sales) to the text in the Marks pane. The text table will look like the below fig:
Image source: Author
Step 2: Click the Analysis tab and select Totals à Show Row Grand total
Image source: Author
Step 3: Sort(by descending) the resulting grand total.
Image source: Author
Step 4: Create 2 filters. One for YEAR(Order Date) and the other for Category. For this drag the year(order date) to filter and select all year values and click ok. Then click on the small triangle symbol and select show filter. To create another filter for Category, drag and drop the category to the filter section. Select all the 3 categories and click ok. Now select the show filter by clicking on the small triangle. The text table will look like this fig. below.
Image source: Author
Step 5: Create a calculated field to highlight the max and min sales in each month. For this, we are using the window_min and window_max functions.
Image source: Author
Step 6: Now drag the min_max to the colors under the marks pane. Click on the triangle in the min_max pill. Select compute using à table down. We want to compute the max and min sales per month wise so we are choosing table down.
Image source: Author
Step 7: Under the marks pane change the chart type from automatic to squares.
Image source: Author
Step 8: We need to highlight the max sales in blue and the min sales in red. So go to AGG(max_min) and edit colors
Image source: Author
Step 9: Change the color palette from Automatic to Red-Blue-White diverging. Select stepped color and set the value to 3. Select Include totals. Click ok.
Image source: Author
Step 10: Now Change the Category filter display from custom list to a multiple values drop down by clicking on the small triangle on the right side of the filter.
Image source: Author
Step 11: Change the Year(order date) filter from custom list to single value slider as shown in the fig below.
Image source: Author
Step 12: Click the format tab on top and click borders. Format the values from none to dotted and choose the color black.
Image source: Author
Step 13: Now there are some null values in the table. Create a Calculated field with ISNULL function to convert the null value to 0.
Image source: Author
Step 14: Drag the null to zero calculated field to text under the marks pane. Now our null values are changed to zero.
Image source: Author
Step 15: To add the currency symbol in front of sales, go to the sum of sales in the marks pane and right-click. Under default à numbers, select currency custom, and make the decimal places 0 points.
Image source: Author
We finally made it. By the end of these challenges, I learned something new. When you keep working on challenges like this you will learn many shortcuts, pay attention to details that were missed earlier, and learn how simple formatting can make your work stand out from others, I encourage all beginners to try challenges like these, and don’t worry if you fail or stuck up somewhere. There are solutions available to all these challenges. Data analysis becomes handy when you work more with different data sets. You will learn to pay attention to small details which can make a huge impact on decision-making.