forums

Use these forums to ask questions and discuss Tableau.

Accessing Fiscal Year in a calculation

Posted May 4th, 2008 by Richard Leeke
in Dates & Times

I am trying to filter a worksheet to show current fiscal year data. I have set the Fiscal Year Start to April for the relevant date, so YEAR([txn_date]) for last month's data shows correctly as FY 2009 when displayed, but I can't spot a way to get at that in a calculation.

I was hoping to use something like the following in a filter:

DATENAME('year', [txn_date]) = DATENAME('year', today())

but that uses the actual year not the fiscal year, so I get everything from January 2008, rather than starting from April. DATETRUNC(), DATEPART() and YEAR() all do the same.

Is there any way to get at the fiscal year in a calculation? Not a big deal - I can always use a slightly longer expression which embeds the fiscal year start month - it would just be a bit more elegant to do it directly.

Add new comment

Comments

On May 5th, 2008 Erin Easter said:

Hi Richard,

So when you make a date field a fiscal date you are just changing how it is displayed in Tableau. So yeah, when you use the DATEPART function in a calculated field you will get back the underlying date rather than the fiscal date. In this case you'll probably have to write the longer expression that defines the fiscal year start month.

reply
On May 5th, 2008 Richard Leeke said:

Thanks, Erin - that is what I'd done. It actually gets quite messy if you want to do a lot of calculations in terms of fiscal years. I couldn't see any way of avoiding repeating the logic either in several filter formulae or in several calculated fields.

A FISCAL_YEAR() function might be a useful little enhancement to add to the wishlist. It would really need to take 2 parameters (date and start_month) so that it can be applied to any date expression - as opposed to relying on the start_month associated with a date field.

Actually what this highlights to me is that it would be really useful to support used defined functions in Tableau (unless they are there already and I've missed them!).

reply
On May 5th, 2008 Erin Easter said:

While user defined functions are not supported in the way you've described them, you could use the RAWSQL functions. When you use these functions you can actually pass a function straight to the underlying database that Tableau may not understand but the database does. Obviously these functions don't really work if you are using a local file data source such as Excel, Access, and Text files. However, you can always create a Tableau Data Extract by selecting Data > Extract. Then you can start using the RAWSQL functions.

reply
On May 5th, 2008 Richard Leeke said:

So is it possible to have user defined functions within a TDE data source? In this case could I create a function fiscal_year(date, start_month), or would I still need to pass the whole calculation as a SQL expression each time? It would be nice to have a way of defining the logic once and then calling that with parameters.

reply
On May 6th, 2008 Aaron said:

As long as you are not using a continuous date axis, you can create a FiscalYear calculated field. By nesting a DATEADD within a DATEPART, calculate the fiscal year for any date by first subtracting three months from the date in question:

DATEPART('year',DATEADD('month',-3,[Date]))

Also, although not exactly a user-defined function, you can use calculated fields to hold a 'parameter,' or a constant value that you use in other calculated fields or filters.

Name: FiscalYearStartMonth
Formula: 4

This field simply holds the Fiscal Year start month, any value between 1 and 12

Name: FiscalYear
Formula: DATEPART('year',DATEADD('month',1-FiscalYearStartMonth,[Date]))

This is identical to the first formula shown, above, except that it uses the parameter to calculate the fiscal year. You could have many such formulas or filters that depend upon a single 'calculated field' that actually holds a constant, and then change the value of the constant to affect changes across your workbook.

reply
On May 6th, 2008 Richard Leeke said:

Much nicer than my way, avoiding lots of repetition of logic, thanks Aaron.

UDFs would still be a nice feature one day... ;-)

reply