Today I will be working through how to produce a graph, where when selecting a point for a specific month, the bar chart beneath will show how the other months vary in sales from the selected.
Step 1: Open tableau, connect the superstore data set and open up a fresh worksheet.
Step 2: Lets create the initial line chart. First, right click on the order date field in the data pane, drag to columns, and when the pop up appears, select the green month option, this gives us the dates at a month level, in a continuous form. Now we will also drag sales onto rows. Double check that the sales field has automatically been set to SUM, if not change it to SUM. Then, if your graph hasn't automatically shown up as a line chart, select line chart from the dropdown in the marks card.
Step 3: Now we have our line chart, lets start setting ourselves up for the difference calculation. Since we want the user to be able to select a point related to a date, lets create a parameter called Date Parameter. To do this, find the order date field in the data pane, and right click, find create, and then select parameter. Set the data type to a date, and allowable values to all, then OK, we have made our parameter! Right click on the parameter in the data pane and press show parameter, this way we can see which date the parameter is currently set to on the right.
Step 4: Now we want to have a reference line on our line chart that aligns with the date available in our Date Parameter. To do this right click on month of order date, and select Add Reference Line. In the value field select our Date Parameter, I'll set my label to value, and then press ok. Now you should have a reference line on the date shown in the Date Parameter card on the right of the sheet.
Step 5: Now we have our parameter and reference line set up, we want to be able to click on a point on our line chart, and the reference line to move to that point. To do this we need to set up an action on our parameter. Go to the worksheet tab in the toolbar at the top, and select Actions. In the bottom left of that pop up, select add action then change parameter. Call this action change date parameter. Select sample superstore from the source sheet drop down, and then in the list of values that appears, ensure that only the current sheet is selected (Top Tip: the reason why we don't select the sheet in the source sheet drop down, but instead do it via the superstore is because doing it this way allows the action to work when the sheet is on a dashboard, whereas if source sheet was sheet, it would only work within the worksheet). Our target parameter will then be Date Parameter, and source field will be MONTH(Order Date), which is the axis on our line chart. Press ok, and now on your line chart when you select a point, the reference line should move, and in the Date Parameter card on the right, the date should change to the selected date.
Step 6: Now we have our parameter, reference line and parameter action set up, we can get started on calculating our difference! First of all, we want to find the total sales on the day that is selected. I have attached a screenshot of this calc below, and now I will explain the calculation. Start by creating an IF statement. We want to say, IF our date parameter is equal to the date on the axis, THEN count the sales value. We DATETRUNC the order date to the month level, since our date parameter is at the month level too. Now we want to wrap that entire expression in a SUM, so we add up all of those sales. Then we want to wrap that entire expression in a FIXED function. The next step involves taking the sales of any given month, and taking away the sales from the selected month. In order to apply the sales from the selected month to every row, we need to use FIXED, otherwise that value will only be present on the row for the selected date, and none of the others.
Step 7: This is the last calculation that needs to be done. Now we just want to calculate the total sales for each month, and take away the sum of sales for the selected month! Call this calculation Difference from date selected.
Step 8: Now we can drag our Difference from date selected onto rows, change the chart type to bar chart, and also drag Difference from date selected onto colour for the bar chart. Now, when you select a point on the line chart, the bar chart below will update with the difference in SUM of sales for the selected month!
