4 Replies Latest reply on Oct 12, 2016 10:28 AM by Sreekanth Kasaraneni

# Calaculating Fiscal year and Quarter from 2 different dates

Hello Team,

I have created Fiscal year and quarter for Date 1 using Date1->default properties->fiscal year->start month to April.

And similarly for Date 2 fiscal year and quarter.

I need to compare Date 1 and Date 2 fiscal year and quarters respectively.

i.e. check Date 1 fiscal year and quarter on its basis it should display sales value of previous 2 quarters and next 2 quarters of Date 2.

So i have created a calculated field  : Select Fiscal Yr- Qtr :   "FY "+STR([Date1 (Years)])+" Q"+STR([Date1 (Quarters)]) and placed it in filter shelf.

Whenever user selects a value from Select Fiscal Yr- Qtr the ouput should be sales of previous 2 and next 2 fiscal yr - quarter of Date 2.

for eg: if Select Fiscal Yr- Qtr is FY2016 Q4 then ouput should be sales of  FY2017 Q1 FY2017Q2 and FY2016Q3 and FY2016 Q2

Thanks.

• ###### 1. Re: Calaculating Fiscal year and Quarter from 2 different dates

Hi Namrata,

Thanks,

Sreekanth.

• ###### 2. Re: Calaculating Fiscal year and Quarter from 2 different dates

have attach the sample excel file. @

• ###### 3. Re: Calaculating Fiscal year and Quarter from 2 different dates

Hey Namrata,

I was able to solve this with a bit of a monster calculated field. Like you started, I created a calculated field called [Fiscal Yr- Qtr]. Instead of filtering by it though, I created a parameter from it and created a second calculated field called [Sales for F Yr-Qtr]. In this, there are a series of if else statements with similar logic as below:

if contains([Select Fiscal Yr- Qtr],[Fiscal Yr- Qtr]) then

if datepart('quarter',[Date1])=1 then

//show previous two

if datepart('quarter',[Date2])=4 AND datepart('year',[Date1])-1=datepart('year',[Date2]) then [Sales]

elseif datepart('quarter',[Date2])=3 AND datepart('year',[Date1])-1=datepart('year',[Date2]) then [Sales]

//show next two

elseif datepart('quarter',[Date2])=2 AND datepart('year',[Date1])=datepart('year',[Date2]) then [Sales]

elseif datepart('quarter',[Date2])=3 AND datepart('year',[Date1])=datepart('year',[Date2]) then [Sales]

end

end

This first sees what is selected from the parameter and based on that, depending on the quarter, it shows the previous and next two FY/Q sales. There probably is a way to clean this up but I am not sure if it is worth it yet because depending on how many rows of data you have, this solution may result in an incredibly slow dashboard.

Check out the attached workbook in Tableau 10.0. If you don't have that version, let me know and I can paste the entire calculation in a reply instead. I hope this is helpful!

-Lisa

CoEnterprise | Home

• ###### 4. Re: Calaculating Fiscal year and Quarter from 2 different dates

Hi Namrata,

is your data continuous with all years present like 2015, 2014, 2013, 2012 and so on

i have Pivoted two date fields in to single date, is this approach correct.

i wrote a calculated field which shows data for previous 2 quarters and next 2 quarters from the selected Year Quarter Parameter

(int(left([Parameter 1],4))-1=[Date (Years)]

or

int(left([Parameter 1],4))=[Date (Years)]

)and

(int(right([Parameter 1],1))-2=[Date (Quarters)]

or

(LEFT([Parameter 1],4)=STR([Date (Years)])

and

(int(RIGHT([Parameter 1],1)))-1=INT([Date (Quarters)])))

OR

(int(left([Parameter 1],4))+1=[Date (Years)]

or

int(left([Parameter 1],4))=[Date (Years)]

)and

(int(right([Parameter 1],1))+2=[Date (Quarters)]

or

(LEFT([Parameter 1],4)=STR([Date (Years)])

and

(int(RIGHT([Parameter 1],1)))+1=INT([Date (Quarters)])))

This will work only if the data is continuous and below image highlight the fields created in the workbook.