Check out the attached workbook. It creates a custom date hierarchy. I only used the Week column of your data to create a calculated field date. The code looks like this:
DATE(MID([Week], 6, 3) + " " + MID([Week], 12))
From there I was able to create a custom date hierarchy that goes to the week of month level. Please let me know if you are looking for something slightly different or if you have any questions.
I was a little confused in your data about the start of the Fiscal Year. I assumed August, but you can change that by right clicking the date fields and selecting "Field Properties" --> "Fiscal Year Start".
Custom Date Hierarchy.twbx.zip 41.9 KB
Thanks for taking the time out and addressing it. It does the solve the issue based on the dummy data that I gave.
However, as I try to replicate this on the real data (coming from the database), I am getting all the Null values (twbx attached). The strings MID([Week], 6, 3) and MID([Week], 12) are parsed successfully, but for some reason the Tableau is unable to make a date here.
Could you look into it as well? Thanks,
Fiscal Week.twbx.zip 50.4 KB
I'm not sure why it is behaving this way. Perhaps, the date format is local or database specific.
I was able to get your workbook to work by changing the calculations slightly.
Comp1 (I'll let you fill in the rest):
CASE MID([Week], 6, 3)
WHEN "SEP" THEN "9"
WHEN "OCT" THEN "10"
Date([Comp. 1] + "/1/" + [Comp. 2])
I've attached the workbook. Hope it helps!
Fiscal Week.twbx.zip 50.1 KB
This is wonderful. Thanks for your help.