1 of 1 people found this helpful
What data are you building off of? If you have a date field, you can right click it and change it's default properties > Fiscal Year Start, and set it as November. then a hierarchy will automatically be created for your criteria.
If you do not have a date field in the data you're building off of, you'll need to create logic to bin the dates into.
Thanks for your answer.
Yes, I have a date field, the problem is that my fiscal year begins the week that contains the 1st of November, this means that sometimes it starts before November 1st.
For example, 2017 began on October 31.
I think what I would suggest is that you create a table that contains the start date and end date for each fiscal year. Something like this (I created it in Excel):
And the formulas I used in the various columns are as follows (in row 2)
StartDate (cell B2): =IF(WEEKDAY(DATE(A2-1,11,1),2)=1,DATE(A2-1,11,1),DATE(A2-1,11,1)-WEEKDAY(DATE(A2-1,11,1),2)+1)
EndDate (cell C2): =B3-1
NumberOfDaysInFiscalYear (cell D2): =C2-B2+1
This is a very odd way of creating Fiscal Years (where it starts on a different date each year), and it creates a very interesting issue, that you can see in the table above: most of your Fiscal Years have 364 days, and every 6th Fiscal Year has 371 days. Odd, but not a problem, as long as you keep track of it.
Then you also need to keep track of how many days each month is supposed to have. In the excel file you attached, most of your fiscal months have 28 days, and a few have 35 days. And it seems that in those 6th-years, where your Fiscal Year has 371 days, the month of November has 35 days, and in all other years it has 28 days. And it seems that all other months have the same number of days regardless of which year it is. This is almost true, except there seems to be some issue in 2000 where 7 days seem to have shifted from January to December - and I don't know why. If there is some additional rule that can cause this sort of a shift, then you will probably have to expand the above table to have a column for each month that contains the number of days that month is supposed to contain. Using that information, you can figure out the starting date for each month.
So, I think if you build out that table, then you should have the information you need in order to be able to determine what fiscal period you are in.
Hope that helps.
It is very interesting your option.
How do you build the second table?
I think that we will have the same problem as 2000 every 20 years.
I really appreciate your time.
Regarding how I built the second table, I am attaching your original excel file, with this table included. You can take a look at the formulas I used to see how it's built. I also used conditional formatting for the yellow highlights.
As I thought about this problem some more, I think your best solution might be to simply use the table you have already built (in your previous attachment), and just carry it out for how many years you need to - such that there is a row for every day, and the columns indicate which fiscal year, fiscal month, and fiscal week, each date corresponds with. Then you can simply join this calendar table with your transactions table on the date field, and all of your transactions will have the appropriate fiscal year, fiscal month, and fiscal week tied to them.
Actually you can use that option - if you create your own Hierarchy. I'm attaching a packaged workbook to show you how this might work.
For example, if you have a transactions table that looks like this:
And your calendar table looks like this:
First, join your transactions table with the calendar table, like this:
Then your dimensions and measures should look something like this:
Convert each of the dimensions to be Discrete rather than continuous (Fiscal Year, Fiscal Quarter, Fiscal Month, Fiscal Week).
Next, right click on Fiscal Year, and select Hierarchy -> Create Hierarchy
Give the new hierarchy a name, like Fiscal Period. And, then, repeat the step for Fiscal Quarter, Fiscal Month2, and Fiscal Week. And you should have something that looks like this:
Then, you can drag the [Fiscal Period] pill to the Rows shelf as a dimension in your viz, and it will look like this - note that on the Rows shelf it does not show the name of the Hierarchy, but, instead it shows the name of the highest level of the hierarcy, in this case, that's Fiscal Year:
Let me know if this is what you had in mind.
FiscalYearExample.twbx 135.9 KB
I think that I will use this option, but I will continue to thinking how I can do the other option.