2 Replies Latest reply on Oct 7, 2016 4:26 AM by praveen p Branched to a new discussion.

    I have the fiscal year and fiscal quarter in the below mentioned format.

    Anandh V

      From the above image i am on FY2016Q4 , here i want to exclude the current/running quarter and i want to show the values of the completed quarters only.

      This has to be happen dynamically, whenever the quarter changes.

      My expected results are

       

      this is what the result i want to display in my sheet.


      In filter also i have show only completed quarters.

        • 1. Re: I have the fiscal year and fiscal quarter in the below mentioned format.
          Carl Slifer

          Hi Anandh,

           

          Try the following calculation. Put it onto your filters shelf and return only the true values.

           

          DATEADD('day',-1,DATEADD('quarter',1,DATETRUNC('quarter',{FIXED [Fiscal Quarter Code]: MAX([Date])}))) < TODAY() 

           

          What it does is for each fiscal quarter code it looks through the dates that comprise it. If the maximum date possible or each quarter inside it is not less than today's date then it will be excluded. If it is less than today's date then it is in the past (it is completed). If we are currently in the middle of the quarter (It's day 7 of Q4 currently for me) and the Q4 fiscal code returns the max date of the 6th of October it will currently include it in that formula. So what we need to do is outsmart it once more and make that field return the last day of it's quarter.

           

           

           

          Phew!

           

          Enjoy,

          Carl Slifer

          InterWorks

          • 2. Re: I have the fiscal year and fiscal quarter in the below mentioned format.
            praveen p

            Hi Anandh,

             

            Please try the below formulas

             

            Quarter:

            DATEPART('quarter',[Date])

             

            Quarter display:

            if { FIXED [Quarter]: COUNTD(DATEPART('month',[Date]))}=3

            then "Q" + STR(int(DATENAME('quarter',[Date])))+"-"+STR(YEAR([Date])) end