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

    Calaculating Fiscal year and Quarter from 2 different dates

    namrata patil

      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
          Sreekanth Kasaraneni

          Hi Namrata,

           

          please provide some sample workbook.

           

          Thanks,

          Sreekanth.

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

            have attach the sample excel file. @

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

              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
                Sreekanth Kasaraneni

                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.

                 

                 

                please find the attached workbook.

                 

                 

                .