Community Month continues and this week Hideaki Yamamoto provided us with this 1 sheet view. He very kindly posted requirements aimed at different levels, but I’m writing the solution for the Level 1 version.

Identifying the Current & Previous FY

The dashboard is all focused on reporting over financial years, and Tableau very kindly allows us to set the start month of the FY, in this case April. Right click on Order Date -> Default Properties -> Fiscal Year Start

If you now double click on Order Date to add it to a new sheet, you automatically get the discrete YEAR part of the Order Date displayed with the relevant FY label.

Expand the field to show the quarter & month, then create an explicit field

Year Order Date

YEAR([Order Date])

convert to discrete, and format as a number with 0dp and no thousand separators.

Add this to the display too and you can see how the dates behave…. every FY start with the month of April, but the FY label is based on the year of the last month (ie March), so FY2024 contains data from April 2023 to March 2024.

Throughout the challenge though, the FY is displayed in the FYXXXX-XX format, and there doesn’t seem to be a way to get a handle on the formatting Tableau applies when the fiscal year is set. So I had to come up with a calculated field to get the FY to display as I wanted.

FY Display

IF MONTH([Order Date])>=4 THEN “FY” + STR(YEAR([Order Date])) + “-” + RIGHT(STR(YEAR([Order Date])+1),2)
ELSE “FY” + STR(YEAR([Order Date])-1) + “-” + RIGHT(STR(YEAR([Order Date])),2) END

Add this to the display (remove the quarter field too).

With this we can identify the maximum FY display (as there’s a requirement not to hardcode anything).

Max FY

{MAX([FY Display])}

Add this to the table

Now we can create a parameter which will display the values of FY Display and automatically show the latest/maximum value by default. Right click on FY Display -> Create -> Parameter

pSelectedFY

string parameter that sets the value to Max FY when workbook opened and lists the value when the workbook opens from the FY Display field.

To identify what records relate to the current or previous year, we create

FY End Year

INT(RIGHT([FY Display],2))

which returns the last two numbers of the FY Display string. Make this a dimension. Then we can get whether the row is related to the FY selected in the parameter by

Is Current Year

INT(RIGHT([pSelectedFY],2)) = [FY End Year]

and

Is Previous Year

INT(RIGHT([pSelectedFY],2))-1 = [FY End Year]

Add the fields onto the table, and show the pSelectedFY parameter

Adjust the parameter to see how the values change. We can now create a field that we can use to filter the data to the rows we’ll need – ie just those for the current year or the previous year

Dates to Display

[Is Current Year] OR [Is Previous Year]

It feels like I created a lot of fields just to get to this point…. there’s probably a more efficient route, but that’s just where my logical next step went to as I built out what I thought I’d need…

Building the basic chart

On a new sheet, add Dates to Display to Filter and set to True.

Add Order Date to Columns and set to the discrete Month level (blue pill). Add Sales to Rows. Add Is Current Year to Colour and adjust accordingly. Re-order so True is listed first.

We need to split the chart by Region, but the headings need to be adjusted based on which region is going to be selected. The selected Region will be stored in a parameter

pSelectedRegion

string parameter defaulted to ‘East’

Show this parameter on the sheet and then create a new field

Region to Display

IF [Region] = [pSelectedRegion] THEN ‘▼’ + [Region]
ELSE ‘►’ + [Region]
END

Add this field in front of MONTH(Order Date) on Columns and Sort based on Region ascending.

We need to show the cumulative sales. We can do this with a quick table calculation on the Sales pill, but sometimes like to create an explicit field, so I know exactly what pill is what

Running Sum Sales

RUNNING_SUM(SUM([Sales]))

This is the same code that a quick table calculation will generate. Format to $ with 0 dp.

Replace the Sales field on Rows with Running Sum Sales and adjust the table calculation setting, so it is computing by Month of Order Date only. Add Order Date to Detail too.

Now, the required solution at Level 3 shows the line (above), the area underneath coloured, and circular markers on the line where the end point is larger than the rest. This ‘feels’ like 3 different marks – line, area and circle, but we can’t do more than 2 mark types with dual axis….

…but we can ‘fake’ it. Now getting the large circle to display was actually part of the challenge that got me stumped, and that I ended up applying at the end after mulling it over with my colleague, Sam Parsons. For the purposes of this blog though, it’s easier to add the relevant logic now.

We want to identify the value associated to the last point for the current year

Last Sales Value

IF LAST() = 0 AND ATTR([Is Current Year]) THEN RUNNING_SUM(SUM([Sales])) END

Drag this onto the Running Sum Sales axis, and drop it when the two green columns appear

This will automatically add Measure Names and Measure Values to the sheet. Move Measure Names from Columns to Detail. Change the mark type explicitly to line. Adjust the table calculation settings of the Last Sales Value field so it is computing by Month of Order Date only. You should notice the end of each ‘current year’ line has a little circle. It’s still a line mark type, but as it’s only 1 point it has no other points to join up to, so looks lie a circle.

We want it to be more prominent though, so move Measure Names from Detail to Size. Reorder the size the fields on the size legend, so the Last Sales Value is bigger. Then from the Colour shelf, add markers to lines

Add another instance of Running Sum Sales to the Rows shelf. This will create a 2nd marks card. Change the mark type of this to Area. Remove Measure Names from this marks card, and adjust the Colour to have an opacity of around 30%. Turn stack marks off (Analysis Menu ->Stack Marks -> off). Set the chart to dual axis and synchronise axis.

Hide the right hand axis, and rename the title of the left hand axis. Format the axis to be $ with 0 dp.

On the bottom half of the chart, we want to display the current year sales as bars with previous year as a reference line, so we need

Sales – CY

IF [Is Current Year] THEN [Sales] END

and

Sales – PY

IF [Is Previous Year] THEN [Sales] END

Format both to $ with 0dp.

Add Sales – CY to Rows which will add a 3rd marks card. Change the mark type to Bar.

Add Sales – PY to Detail. The right click on the Sales – CY axis and Add Reference Line.

Set the reference line to be per cell based on the Sales-PY field, formatted as a line and with a fill below of light grey to give the appearance of a bar.

Change the title of the Sales – CY axis. Remove all gridlines and zero lines. Format the MONTH(Order Date) to use 1st letter only. Hide the null indicator.

Adding the ‘headers’

We need to have 4 rows of headers at the top – currently we’ve got 1 – the Region.

Below Region we want to split the data by Category if its the selected region, so we need

Category to Display

IIF([Region]=[pSelectedRegion], [Category],”)

Add this on to Columns after Region to Display. The visuals should automatically adapt. Adjust the value of the pSelectedRegion parameter to see how the viz changes.

Now double click into the Columns shelf and manually type ‘Total Sales’ (including the quotes). This will create a ‘dummy’ header pill. Move it to be after Category To Display.

Finally, create a new field

Current Year Sales

{FIXED [Region], [Category To Display]: SUM([Sales – CY])}

change this to be a dimension and format to $ with 0dp. Add this field to Columns after Total Sales, and we now have all the header fields we need.

Change the formatting as follows

• Region To Display : Shading navy, font white, size 12, Tableau Medium Bold
• Category to Display : font black, Tableau Medium size 12
• Current Year Sales : font dark teal, Tableau Medium size 14 bold

Adjust the width of each header row to give a bit more ‘breathing room’.

Format the column dividers so the Header level is set to a thick white line, and set the row divider so the header level is set to None

Hide the ‘Region To Display / Category To Display / ‘Total Sales’/… etc heading label (right click and hide field labels for columns). Adjust the font of both axis to be smaller (I set to 8pt).

Adding the Tooltips

Add FY Display, Year Order Date, Region, Sales and Category To Display to the Tooltip shelf of the All marks card.

We need another couple of fields to get the required display.

Month Order Date

MONTH([Order Date])

convert to a dimension and custom format as 00

Tooltip |

IF [Category To Display] <> ” THEN ‘|’ END

Add these fields to the Tooltip shelf too of the All marks card and adjust the tooltip

Adding the sheet title

For the sheet title, we need to display the FY of the previous year

FY Display Prev Year

‘FY’ + STR(INT(MID([pSelectedFY],3,4))-1) + ‘-‘ + STR(INT(RIGHT([pSelectedFY],2))-1)

Add this to the Detail shelf of the All marks card. Then adjust the title of the sheet so its referencing the pSelectedFY parameter and the FY Display Prev Year field.

Adding the interactivity

Add the sheet onto a dashboard. I floated the pSelectedFY parameter and displayed it as a slider but customised to not show the slider.

Create a single dashboard parameter action to select the Region

Set Region

On select of the viz, set the pSelectedRegion parameter passing in the Region field. Set the value to empty when selection is cleared.

And with that, you should have a completed solution. My published viz is here.

Happy vizzin’!

Donna