3 Replies Latest reply on Dec 16, 2016 1:48 PM by Matthew Risley

    Number of quarters in a dataset

    Anna Kisting

      Hi everyone!

       

      I need a formula that calculates the number of quarters in the most recent Fiscal year.  I'm making a label.  If the most recent FY has a full set of data, then I want it to say "FY15  compared to FY16".  If it does not have a full year of data, I want it to say "FY15 compared to FY16 Q1"  I have a Boolean field for current year and I created a date for last year to use in my string.  But when I do it this way, I get the aggregate/non aggregate data error.

       

       

      if [Current Year]=true and COUNTD(DATEPART('quarter', [Ddat]))=4

       

      Then

       

      STR("Year over Year Comparing FY")+

      STR (MAX(DATENAME('year', [Ddat]))) +

      STR(" to FY")+

      STR (MAX(DATENAME('year', [last Year])))

       

      ELSE

       

      STR("Year over Year Comparing FY")+

      STR (MAX(DATENAME('year', [Ddat]))) +

      STR ("Q") +

      STR (MAX(DATENAME('quarter', [Ddat]))) +

      STR(" to FY")+

      STR (MAX(DATENAME('year', [last Year])))

       

      END

       

      I can make a dummy workbook if you want to see the real deal.

       

      Thanks!

        • 1. Re: Number of quarters in a dataset
          Matthew Risley

          Anna,

           

          a workbook may be nice if it's something that you can do

           

          In the meantime i have another possible solution:

           

          What if you used

          if Max(Datepart('month',Date) > 12 then [Do the thing you're trying to do]

          else [do the other thing]

           

          You may have to change the 12 to your custom fiscal year. But what that is saying is, "If the most recent date is December- then i have full data". Now you may have to tweak this to a different 'Date Level' if need be (could even use quarter)

           

          And this solution may not be what you need, just wanted to add a thought

          1 of 1 people found this helpful
          • 2. Re: Number of quarters in a dataset
            Anna Kisting

            Figured it out and I'm not going to tell how much time it took me... 

             

            If

             

            DATEPART('month',max([Ddat]))=6  then....  Else....  End.

             

            Since our FY starts in July - if the max month is 6 (June) then we've got a full year and if not then we don't.

             

            Thanks and have a great weekend!

            • 3. Re: Number of quarters in a dataset
              Matthew Risley

              Your solution is very similar to mine. Glad you figured it out!

               

              Happy Tableau-ing!