How does the company perform in the fiscal year?

Guest poster Ivett Kovacs was back to set the weekly #WOW2020 challenge this week, and delivered a very ‘relatable’ business challenge – Fiscal Year to Date reporting.

I’ve been quite used to doing this type of reporting within my job, so on the whole I found the core requirements pretty straightforward – there’s just a lot of calculated fields 🙂 The trickiest part I found was getting everything organised on the dashboard and making the budget parameters appear/disappear based on the filter selected – this took an extraordinarily looonnnng time even though I knew the technique – more on that later.

Setting up the Fiscal Year

The data source provided was one curated by Ivett. It contained financial ‘transactions’ against Account Codes from Oct 2018 to May 2020.

The date in the file was initially recognised by Tableau as a string, but simply changing it to a Date datatype in the Data Source pane easily resolved this.

The fiscal year starts on 1st October, so once on a sheet, set this by right-clicking on the Accounting Date field and choosing Default Properties -> Fiscal Year Start -> October

If you add Accounting Date to a sheet and expand from Year -> Month, you’ll see that the Year part is now labelled as FY 2019 and FY 2020, and Q1 starts in October

Just a couple of points to be aware of if you’re not familiar with working with Fiscal Years.

  • A relative date filter for ‘this year’ will recognise your fiscal date setting, so if you did this on this data set, you’d get data from Oct 2019 to May 2020.
  • Any date related functions such as YEAR([Date]) or DATETRUNC(‘year’, [Date]) does not recognise the fiscal year setting. So YEAR(#2019-11-01#) will return 2019 and YEAR(#2020-01-01) will return 2020 even though they are both in the same FY 2020 fiscal year. It does mean at times, depending on what you’re building, you may need to hard-code information that defines the start month of your fiscal year.

Building all the calculated fields

First up we need a couple of measures as stated in the requirements.

Sales

IF STARTSWITH([Account Number],’5′) THEN [Value] END

only counts for Account Number starting with a 5

OPP

IF STARTSWITH([Account Number],’5′) OR STARTSWITH([Account Number],’696′) THEN [Value] END

only counts for Account Numbers starting with a 5 or 696.

Then we need lots of fields to help us get all the various data needed. This is simply going to be a big list 🙂

Current Month

DATE(DATETRUNC(‘month’,{FIXED : MAX([Accounting Date])}))

finds the maximum date in the dataset and sets to the 1st of the month, in this instance it will be 01 May 2020. This is a more generic approach than harcoding.

FY Start Current Year

#2019-10-01#

I said there might need to be some hardcoding! Ideally, having set a fiscal year, you’d like a function along the lines of DATETRUNC(‘fiscal year’, [Date]) to give you this value. Apart from setting this value in a parameter, I’m not aware of any way you can determine this date without any sort of hardcoding 😦 This KB article tries to provide some suggestions, but you still need to hardcode a value to ‘shift’ to the appropriate month; in this case we’d have to hardcode 10 as October is the 10th month. Feel free to upvote this idea on the Tableau Forums which asks to address this 🙂

Current FYTD Sales

IF DATETRUNC(‘month’,[Accounting Date]) >= [FY Start Current Year] AND
DATETRUNC(‘month’,[Accounting Date])<=[Current Month] THEN [Sales] END

only return sales values for dates from 01 Oct 2019 to 31 May 2020.

Current FTYD OPP

IF DATETRUNC(‘month’,[Accounting Date]) >= [FY Start Current Year] AND
DATETRUNC(‘month’,[Accounting Date])<=[Current Month] THEN [OPP] END

as above but for the OPP measure.

Current Month Prev FY

DATE(DATEADD(‘year’,-1,[Current Month]))

go back 1 year to 1st May 2019.

Prev FYTD Sales

IF DATETRUNC(‘month’,[Accounting Date])<= [Current Month Prev FY] THEN [Sales] END

Only return sales values for dates up to 31 May 2019. Note the data only starts from 01 Oct 2018, which is the start of the previous FY, but if we couldn’t guarantee that, we’d have stored an ‘FY Start Prior Year’ and added an additional clause to the above calculation.

Prev FYTD OPP

IF DATETRUNC(‘month’,[Accounting Date])<=[Current Month Prev FY] THEN [OPP] END

as above for OPP.

Curr vs Prev Sales Diff

SUM([Current FYTD Sales]) – SUM([Prev FYTD Sales])

the actual difference in sales

Curr vs Prev Sales Diff %

[Curr v Prev Sales Diff] / SUM([Prev FYTD Sales])

the % change in sales

Curr vs Prev OPP Diff

SUM([Current FYTD OPP]) – SUM([Prev FYTD OPP])

the actual difference in OPP

Curr vs Prev OPP Diff %

[Curr v Prev OPP Diff] / SUM([Prev FYTD OPP])

the % change in OPP

To deal with the Budgets, we need to create a couple of parameters Budget Sales (M) and Budget OPP (M). Both are integer parameters defaulted to 2,300 and 3000 respectively.

But because these parameters aren’t actually in millions, we need further fields to translate them into the value we really need for comparisons.

Budget Sales Ref Line

[Budget Sales (M)] * 1000000

Budget OPP Ref Line

[Budget OPP (M)] * 1000000

and now we can compute the differences

Curr vs Budget Sales Diff

SUM([Current FYTD Sales]) – MIN([Budget Sales Ref Line])

We need the MIN() function as the same Budget Sales Ref Line value is stored on every row, so SUM() will multiply the value too much. AVG() or MAX() would have worked just as well.

Curr vs Budget Sales Diff %

[Curr v Budget Sales Diff] / MIN([Budget Sales Ref Line])

and again we need to duplicate these for the OPP measure

Curr vs Budget OPP Diff

SUM([Current FYTD OPP]) – MIN([Budget OPP Ref Line])

Curr vs Budget OPP Diff %

[Curr v Budget OPP Diff] / MIN([Budget OPP Ref Line])

Now we’ve got all the core variables we need, we need to determine which one to use as the comparison in the bar chart and the KPI indicator. This is based on a parameter for the user to decide if they’re comparing Actuals against Budget or against Previous Year Actuals.

Compare Filter

I chose to create an integer based parameter with the display altered to show the relevant text. We will be using this parameter in calculated fields and comparing integers rather than strings is much more efficient (and easier to read).

So with the parameter set up, we now need to create a few fields that we’ll use to show the values we want

Sales Diff

IF [Compare Filter] = 0 THEN [Curr v Budget Sales Diff] ELSE [Curr v Prev Sales Diff] END

This is custom formatted to display as $ in millions with an arrow to show positive or negative : ▲”$”#,##0,,M;▼”$”#,##0,,M

Sales Diff %

IF [Compare Filter] = 0 THEN [Curr v Budget Sales Diff %] ELSE [Curr v Prev Sales Diff %] END

This is formatted to 1 decimal place.

Opp Diff

IF [Compare Filter] = 0 THEN [Curr v Budget OPP Diff] ELSE [Curr v Prev OPP Diff] END

Same custom formatting as above.

Opp Diff %

IF [Compare Filter] = 0 THEN [Curr v Budget OPP Diff %] ELSE [Curr v Prev OPP Diff % ] END

Sales Ref Line

IF [Compare Filter] = 0 THEN MIN([Budget Sales Ref Line]) ELSE SUM([Prev FYTD Sales]) END

This is used for the line shown on the bar chart, and we’ll need the same for the Opp measure.

Opp Ref Line

IF [Compare Filter] = 0 THEN MIN([Budget OPP Ref Line]) ELSE SUM([Prev FYTD OPP]) END

Finally we need a couple of fields to use to work what colour the bars need to be.

Colour : Sales Diff

IF [Sales Diff %] <-0.05 THEN ‘Difference < -5%’ ELSEIF [Sales Diff %] > 0.05 THEN ‘Difference > 5%’
ELSE ‘-5% <= Difference <= 5%’
END

Colour : OPP Diff

IF [OPP Diff %] <-0.05 THEN ‘Difference < -5%’ ELSEIF [OPP Diff %] > 0.05 THEN ‘Difference > 5%’
ELSE ‘-5% <= Difference <= 5%’
END

Note – my published solution has something slightly longer winded as when I originally built the viz, I created the colour fields before I created the generic Diff/Diff% fields referenced above.

Right! That’s a LOT of calculated fields (I did warn you!). In some cases it may have been possible to combine, but I like creating building blocks to keep things simpler to read.

Sales YoY Trend Line

The basis of this type of chart is pretty much Desktop 101.

  • Month(Accounting Date) on Columns (blue pill)
  • Sales on Rows
  • Year(Accounting Date) on Colour with colours adjusted accordingly.
  • Add the average line and Label the most recent point
  • Format the gridlines/rows etc and axis

The most recent mark is a larger circle. We need another calculated field for this

Current Month Sales

IF DATETRUNC(‘month’,[Accounting Date]) = [Current Month] THEN [Sales] END

This just stores the sales value for the latest month.

Add this field to the Columns and make dual axis and synchronise axis. Adjust the marks back to be a line and a circle and adjust the Sizes to suit.

Duplicate all the above on another sheet for the OPP values instead.

Sales Bar Chart

Simple bar chart

  • Current FYTD Sales on Columns
  • Colour : Sales Diff on Colour
  • Sales Ref Line on Detail
  • Add Sales Ref Line as a reference line and label & format to suit.
  • Label bar and align middle right; format to suit.

The colour legend will only display a single option at a time. You’ll need to set the Compare Filter to compare against budget and show, then adjust, the value of the Budget Sales (M) parameter to values that go beyond the thresholds, to set the other colour options.

Repeat all this again on another sheet for the bar displaying the OPP measure.

Sales KPI

Simply add Sales Diff & Sales Diff % to the Text. Format row/column lines to suit.

Again, repeat for the equivalent OPP measures.

Year Legend

The standard legends aren’t used as these are only square icons, but the challenge shows circles. So a custom legend sheet is created as below

The hidden axis has been fixed from 0.45-1 to push the display to the left more.

% Diff Indicator Legend

Again the standard legend can’t be used this time, as only 1 option ever shows. So this is a custom ‘fake’ legend.

I simply used the Account Number field for this purpose. I filtered a sheet to just 3 different Account Numbers. I then built a similar viz to the above, but using Account Number throughout.

The sneaky trick I used here was to simply ‘alias’ the actual Account Numbers displayed to the text I wanted.

Building the dashboard

Getting all the objects in the right places can be a bit of trial and error. All the objects on my dashboard, apart from the Budget parameters, are tiled using a mix of horizontal and vertical containers, nested within.

Hide/Show the Budget Parameters

This probably took me the most time. It’s using a technique sometimes referred to as Parameter Popping. It uses containers and works similarly to sheet swapping.

I had to build a ‘blank’ sheet for this – a sheet that will show (a blank value) and hide based on the Compare Filter parameter.

I needed another field

Is Prev Value to Compare

[Compare Filter]=1

This returns true if the parameter is set to compare against the Previous Year rather than the Budget.

This is added as a Filter to the blank sheet and set to True, which means the sheet will ‘show’ when comparing to Previous Year, and hide when comparing to Budget.

A floating horizontal container is then added to the sheet, and all the objects – the Budget parameters, some Text boxes to label the parameters and the ‘blank’ sheet are ‘carefully’ added. The blank sheet should be the first object on the left. I say ‘carefully’ as it took a lot of trial and error to make it work. If it is done right, then changing the Compare Filter parameter should make the budget parameters move left & right as the blank sheet shows and then hides.

The floating horizontal container is made wider than the dashboard itself, so the showing of the ‘blank’ sheet, pushes the other objects so far to the right that they’re not on the dashboard at all.

The screen shots below show what it looks like in Desktop

As I said, Parameter Popping can be a bit tricky – I only used it twice, so haven’t yet found a sure fire way to get it right first time. If you google ‘Tableau Parameter Popping’ you’ll find a few links that might help, and/or check out Week 4 of #WOW2020 which also uses it (the other time I’ve used it).

And that’s about it. My published viz is here. Enjoy!

Happy vizzin’! Stay Safe!

Donna