10 Replies Latest reply on Nov 19, 2018 10:35 AM by Paul Wachtler

# How to calculate the days in Fiscal Year

Hi All,

I am trying to figure out lost wages per fiscal year based off of avg. weekly wages. To do this, I need to know how many weeks they missed in that fiscal year. For a claim that includes only one fiscal year, this is easy. However, I'm not sure how to do this if there are multiple years. My coworker told me that she read somewhere that Tableau has an easy function to accomplish this.

Please let me know if this is possible or not.

Thanks,

• ###### 1. Re: How to calculate the days in Fiscal Year

Hi Austin,

You can use an LOD calculation for this.  Something like:

{fixed [FY]: sum( ( [Lost Days/7) * [Average Weekly Wage])}

For each fiscal year, that will take the number of lost days, divide them by 7 to figure out the number of weeks, and then multiply that by the average weekly wage.

Let me know if you have any questions.

Best,

Paul

1 of 1 people found this helpful
• ###### 2. Re: How to calculate the days in Fiscal Year

Thanks Paul, I need it to display it for multiple fiscal years, but I only have the start date and end date. For example, on claim number 1, the start date is 10/26/2017 and ends 11/7/2019. This will span 3 fiscal years and I do not have a column for every fiscal year, just the start and ending fiscal years. I would like some way to figure out what extra fiscal years are within that range and apply them.

Let me know if that makes more sense.

• ###### 3. Re: How to calculate the days in Fiscal Year

Hi Austin,

That makes sense, but Tableau needs a data source with your Fiscal Years listed to join against your current data.  That way it could join all 3 fiscal years against that one row of data.

You'd have your data source of fiscal years like this:

[Fiscal Year]

2016

2017

2018

2019

Then you'd left join your current dataset against that where your first FY field >= Fiscal Year and your second FY field <= Fiscal Year.  This will duplicate your rows for each Fiscal Year it applies to.  Then you can use that LOD from before to calculate your lost wages.

1 of 1 people found this helpful
• ###### 4. Re: How to calculate the days in Fiscal Year

Thanks for the help! I am having trouble with it displaying only the loss amount for that fiscal year. If you could tell me what I did wrong, that would be great.

• ###### 5. Re: How to calculate the days in Fiscal Year

Also, I have just realized that the numbers are not displaying as an aggregate. If the loss amounts per category were: 1,2,3,4, then it would display 4. I don't really understand why though. The formula makes sense, but I'm not really familiar with the fixed function.

• ###### 6. Re: How to calculate the days in Fiscal Year

Your original question asked about calculating the average lost wages, so that's what the current LOD does.

If you want to calculate the number of Lost Weeks, you just need to remove the part where you multiply by the average weekly wage.

{fixed [FY]: sum( [Lost Days]/7) }

I'm not sure what you mean by numbers not displaying in aggregate.  What do you want that part of your viz to look like?

1 of 1 people found this helpful
• ###### 7. Re: How to calculate the days in Fiscal Year

I want to display the total lost each fiscal year per the type of claim. I have attached a version that has 2 sheets; one with how I would like it to display and one how the total should show as. Since a claim can only have one type, if you add all of their values, then the sum should be equal to the value if you didn't split it by type of claim. Does that make sense?

• ###### 8. Re: How to calculate the days in Fiscal Year

Oh I got it now.  The first thing to fix is that your Fiscal Year field coming from your new data source is called [Fiscal Year] so you need to replace [FY] in the LOD with that:

Calculation 1

{fixed [Fiscal Year]: sum( ( [Lost Days]/7) * [Average Weekly Wage])}

Now for the average lost wages by claim type, you don't even need an LOD since your view is already at the right level of detail - Fiscal Year and Type of Claim.  The LOD above is not including the Type of Claim.  So you can either do this:

sum( ( [Lost Days]/7) * [Average Weekly Wage])

or if you want to keep it as an LOD if you'll be using it on another view at a different level of detail, you can use this:

{fixed [Fiscal Year], [Type Of Claim]: sum( ( [Lost Days]/7) * [Average Weekly Wage])}

1 of 1 people found this helpful
• ###### 9. Re: How to calculate the days in Fiscal Year

It worked! Thank you!

• ###### 10. Re: How to calculate the days in Fiscal Year

Welcome!