Go through below steps.
1) Create Calculations Using the Date Functions.
2) Similarly Create Month, Date Calculations.
3) Crate Semester using the Syntax which was used in the below Image.
4) Create Another Calculated field for Date.
5) Drag Year, Month Fields from Measures Window to Dimensions Window.
6) Create a Hierarchy by right click on "Year" field.
7) Drag the Semester, Month, Date1 Fields into the Hierarchy or Right click on these fields , Choose Add to Hierarchy.
9) Arrange the Level From Top to Bottom Accordingly by dropping. (Year -> Semester -> Month -> Date1)
10) Now we are having the Time Dimension With New Level "Semester".
11) We can find the Hierarchical Relationship.
I hope it will Help you.
We can define the calendar type we are following by selecting the Starting of the Month.
Then We can define Quarters, Semesters Manually.
The following images are showing Indian Financial Calendar.
1) The following image is representing how to define our Calendar type. (Starting month of the Year.)
2) Indian Finical Calendar
I have created the Indian Financial Calendar with Hierarchical Levels.
Before create the Hierarchy
After create the Hierarchy
I have used the Following Calculations to create the above Hierarchy Levels.
if [Quarter - Fin]= "Q1" or [Quarter - Fin]= "Q2" then "Sem1" else "Sem-2" end
if datepart('month', [Date]) =4 or datepart('month', [Date]) =5 or datepart('month', [Date]) =6 then "Q1"
elseif datepart('month', [Date]) =7 or datepart('month', [Date]) =9 or datepart('month', [Date]) =8 then "Q2"
elseif datepart('month', [Date]) =10 or datepart('month', [Date]) =11 or datepart('month', [Date]) =12 then "Q3"
elseif datepart('month', [Date]) =1 or datepart('month', [Date]) =2 or datepart('month', [Date]) =3 then "Q4" end
Prepare Your Financial Calendar by following the Above syntaxes.
Note: Sequence of creating the Above Calculations.
1) Month 2) Quarter 3) Semester 4) Year
Hence you can define the Hierarchy.
I hope it will help you.
Hi Laxman Kumar,
I am having a similar issue with different data. I need to create a way in which I can fetch the last 1 year data in tableau using fiscal year and month alone. I do not have date field in my data. Could you please help me with this.
I am unable to attach the format so I have added it in the discussion that I have raised.
This was very helpful. However my problem is that I have semester wise data and in order to do semester over semester calculations, Tableau still takes it as month when I make it as semester.
so for Wave 1 2018 (which is read as Jan 2018), the previous wave by Tableau is Dec 2017 but it should be Wave 2 2017 (read as Feb 2017) as data is only for Jan 2017, Feb 2017, Jan 2018 if I convert the string column of data into Date.
Please refer to my post on Need help with Custom date calculations
Any help will be appreciated.